Oracle E-Biz R12 채워가는 팁

Oracle E-Biz R12 채워가는 팁

akashicRec.egloos.com/526029

1. confirm메세지(2013.03.26)

fnd_message.set_name('FND', 'confirm하시겠습니까?'); 

if fnd_message.question( 'YES', 'NO', null, 1, 2, 'CAUTION' ) = 1 then

    --yes

else

    --no

end if;

2. AP Invoice 상태 확인

SELECT APPS.AP_INVOICES_PKG.GET_APPROVAL_STATUS ( I.INVOICE_ID ,

                                                                                                   I.INVOICE_AMOUNT  ,

                                                                                                   I.PAYMENT_STATUS_FLAG ,

                                                                                                   I.INVOICE_TYPE_LOOKUP_CODE ) Approval_Status ,

                    invoice_num 

          FROM AP_INVOICES I 

        WHERE invoice_num = '12345'; 

        SELECT invoice_num ,

                     approval_status_lookup_code 

           FROM ap_invoices_v 

         WHERE invoice_num = '12345';

3. OPP로그 경로 확인

SELECT fcpp.concurrent_request_id req_id, fcp.node_name, fcp.logfile_name
FROM fnd_conc_pp_actions fcpp, fnd_concurrent_processes fcp
WHERE fcpp.processor_id = fcp.concurrent_process_id
AND fcpp.action_type = 6
AND fcpp.concurrent_request_id = <request_id>;

4. Descriptive Flex Fields Find Query

SELECT V.APPLICATION_ID, 

FA.APPLICATION_NAME, 

FD.TITLE, 

V.descriptive_flex_context_code, 

V.end_user_column_name, 

v.APPLICATION_COLUMN_NAME, 

F.FLEX_VALUE_SET_NAME, 

DECODE(f.VALIDATION_TYPE,'F', 'TABLE', 

'I','INDEPENDENT', 

'D', 'DEPENDENT', 

'N', 'NONE', 

'P', 'PAIR', 

'U', 'SPECIAL', 

'X', 'TRANSLATABLE INDEPENDENT', 

'Y', 'TRANSLATABLE DEPENDENT') VALIDATION_TYPE,

v.descriptive_flexfield_name

FROM fnd_descr_flex_col_usage_vl V, 

FND_FLEX_VALUE_SETS F, 

FND_APPLICATION_TL FA, 

FND_DESCRIPTIVE_FLEXS_VL FD 

WHERE --V.descriptive_flexfield_name NOT LIKE '$SRS$.%' 

1=1

--and FD.TITLE LIKE '%Gene%'

and v.APPLICATION_COLUMN_NAME = 'ATTRIBUTE10'

AND V.FLEX_VALUE_SET_ID = F.FLEX_VALUE_SET_ID 

AND V.APPLICATION_ID = FA.APPLICATION_ID 

AND FA.APPLICATION_ID = FD.APPLICATION_ID 

AND V.DESCRIPTIVE_FLEXFIELD_NAME = FD.DESCRIPTIVE_FLEXFIELD_NAME

4. block 의 상태를 강제로 query 상태로 변경

FOR R IN C1

LOOP  

L_COUNT := L_COUNT + 1;

:SEGMENT4_DETAIL.CHECKED          :=  R.CHECKED;

:SEGMENT4_DETAIL.SEGMENT4       :=  R.SEGMENT4;

:SEGMENT4_DETAIL.SEGMENT4_NAME   :=  R.SEGMENT4_NAME;

set_record_property( Name_In('system.cursor_record'), Name_In('system.cursor_block'), STATUS, QUERY_STATUS);

NEXT_RECORD;  

END LOOP;

5. 시스템 메세지 enabled

:system.message_level := 25;  -- 메세지 출력안함

:system.message_level := 0;   -- 원복

6. forms 내에서 DML 구문으로 데이터 입력/변경시 commit방법

forms_ddl('COMMIT');

7. pre_query에서 like 구문 사용하기

SET_BLOCK_PROPERTY('blcok', ONETIME_WHERE, 'FIELD LIKE :CONTROL.value');

SET_BLOCK_PROPERTY(’BLCOK’,DEFAULT_WHERE,’FIELD IS NULL’); -- IS NULL 설정

8. block을 이용하여 loop돌리기

        go_block('block_name');

  FIRST_RECORD;

           LOOP

   --실행구문

 

 IF :SYSTEM.LAST_RECORD = 'TRUE' THEN

  EXIT;

 ELSE

  NEXT_RECORD;

 END IF;

 

 END LOOP;

9. 메세지 박스 띄우기

FND_MESSAGE.DEBUG('메세지박스를 띄우고 종료합니다.');

RETURN;

10. 숫자 포멧 적용시키기

FM999,999,999,999

11. Form에서 다른 Form 열기(R12)

declare v_parameters varchar2(4000);

begin

    v_parameters    := ' param1 = "' || :test.value1 || '"' ||

                               ' param2 = "' || :test.value2 || '"';

     

    fnd_function.execute( FUNCTION_NAME => 'form_name'

                        , OPEN_FLAG     => 'Y' 

                        , SESSION_FLAG  => 'Y'

                        , OTHER_PARAMS  => v_parameters

                        ); 

end;

12. 여러 레코드중 특정 조건에 해당하는 레코드만 입력 가능하게 하기

Set_Item_Instance_Property( 'block.field', CURRENT_RECORD, INSERT_ALLOWED ,PROPERTY_TRUE);