How to schedule PO workflow schedule process

create or replace PACKAGE APPS.XXXX_PO_WF_SCHEDULING_PKG

IS

   --|===========================================================================|

   --|                       Client A                                         |

   --|                                                                           |

   --| Description    : This Package is used to schedule the PO auto approval    |

   --|                  workflow program                                         |

   --| Program Name   : XXXX_PO_WF_SCHEDULING_PKG_EXE                            |

   --| Module Name    : PO                                                                                                                   |

PROCEDURE XXXX_CALLING_POAPPRV (P_errbuf out varchar2, P_retcode out varchar2, p_org_id number);

END XXXX_PO_WF_SCHEDULING_PKG;

/

SHOW ERRORS;

create or replace PACKAGE BODY APPS.XXXX_PO_WF_SCHEDULING_PKG

IS

   --|===========================================================================|

   --|                       Client A                                          |

   --|                                                                           |

   --| Description    : This Package is used to schedule the PO auto approval    |

   --|                  workflow program                                         |

   --| Program Name   : XXXX_PO_WF_SCHEDULING_PKG_EXE                            |

   --| Module Name    : PO                                                                                                                        |

PROCEDURE XXXX_CALLING_POAPPRV (P_errbuf out varchar2, P_retcode out varchar2, p_org_id number)

IS

-- Initializing the profile vaues into local variables.

ln_user_id      number(30);

ln_resp_id      number(30);

ln_resp_Appl_id number(30);

xv_item_key     varchar2(100);

--declaring the cursor

CURSOR po_details_cur

IS        

select segment1,po_header_id,default_approval_path_id,document_type_code,document_subtype,org_id,agent_id  from 

(SELECT pha.segment1 segment1,

pha.po_header_id po_header_id,

pdt.default_approval_path_id default_approval_path_id,

pdt.document_type_code document_type_code,

pdt.document_subtype document_subtype,

pha.org_id org_id,

pha.agent_id agent_id

--pha.authorization_status authorization_status,

--pdt.wf_approval_itemtype wf_approval_itemtype,

--pdt.wf_approval_process wf_approval_process

--pha.agent_id agent_id,

--prha.AUTHORIZATION_STATUS req_auth,

--pha.creation_Date

FROM     apps.PO_HEADERS_ALL PHA

        ,apps.po_document_types_all pdt

        ,apps.PO_LINES_ALL PLA

        ,apps.po_line_locations_all plla

        ,apps.po_distributions_all pda

        ,apps.po_req_distributions_all prda

        ,apps.po_requisition_lines_All prla

        ,apps.po_requisition_headers_All prha

WHERE 1=1

--AND PHA.SEGMENT1 ='13033126' -- '13033211' --in ('13033163','13033161','13033162','13033145')

AND pha.authorization_status in ('INCOMPLETE')   -- ,'REQUIRES REAPPROVAL')

AND pha.type_lookup_code = pdt.document_subtype

AND pha.org_id = pdt.org_id

AND pha.org_id = (select fnd_profile.value('ORG_ID') from DUAL)

AND pdt.document_type_code = 'PO'

AND PHA.PO_HEADER_ID = PLA.PO_HEADER_ID

AND PLA.PO_LINE_ID = plla.po_line_id

AND plla.line_location_id = PDA.line_location_id

AND PDA.req_distribution_id = prda.distribution_id

AND prda.REQUISITION_LINE_ID = prla.REQUISITION_LINE_ID

AND prla.REQUISITION_header_ID = prha.REQUISITION_header_ID

AND prha.TYPE_LOOKUP_CODE = 'PURCHASE'

AND prha.AUTHORIZATION_STATUS = 'APPROVED'

AND to_date((TO_CHAR(PHA.CREATION_DATE,'DD-MON-YYYY HH24:MI:SS')),'DD-MON-YYYY HH24:MI:SS') >= (SELECT to_date((TO_CHAR(REQUESTED_START_DATE,'DD-MON-YYYY HH24:MI:SS')),'DD-MON-YYYY HH24:MI:SS') FROM (SELECT REQUEST_ID,REQUESTED_START_DATE 

                                                               FROM apps.FND_CONCURRENT_REQUESTS 

                                                               WHERE CONCURRENT_PROGRAM_ID = (SELECT CONCURRENT_PROGRAM_ID 

                                                                                              FROM apps.FND_CONCURRENT_PROGRAMS FCP 

                                                                                              WHERE FCP.CONCURRENT_PROGRAM_NAME = 'XXTT_PO_WF_SCHEDULING_PKG_EXE')

                                                                 AND STATUS_CODE = 'C'

                                                               ORDER BY REQUESTED_START_DATE DESC) CON_REQ_DETAILS

                           WHERE ROWNUM <= 1)

-- AND ((PRHA.INTERFACE_SOURCE_CODE IS NULL) or (PRHA.INTERFACE_SOURCE_CODE in ('ORDER ENTRY','INV','WIP','MSC')))

AND (PRHA.INTERFACE_SOURCE_CODE in ('ORDER ENTRY','INV','WIP','MSC'))

GROUP BY pha.po_header_id,pha.segment1,pdt.default_approval_path_id,pdt.document_subtype,pdt.document_type_code,pha.org_id,pha.agent_id

ORDER BY pha.po_header_id DESC) a

WHERE rownum <= 4;

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

  -- Initializing the profile vaues into local variables.

ln_user_id      :=  apps.fnd_profile.value('USER_ID');

ln_resp_id      :=  apps.fnd_profile.value('RESP_ID');

ln_resp_Appl_id :=  apps.fnd_profile.value('RESP_APPL_ID');

apps.fnd_global.apps_initialize (user_id      => ln_user_id,

                                 resp_id      => ln_resp_id,

                                 resp_appl_id => ln_resp_Appl_id);

FOR po_details_rec IN po_details_cur

LOOP

         apps.mo_global.init (po_details_rec.document_type_code);

         apps.mo_global.set_policy_context ('S', po_details_rec.org_id);

         SELECT po_details_rec.po_header_id || '-' || TO_CHAR (po_wf_itemkey_s.NEXTVAL) INTO xv_item_key FROM DUAL;

        fnd_file.put_line(fnd_file.log,'Calling po_reqapproval_init1.start_wf_process Oracle PO=> '|| po_details_rec.segment1);

        DBMS_OUTPUT.PUT_LINE('Calling po_reqapproval_init1.start_wf_process Oracle PO=> '|| po_details_rec.segment1);

         apps.po_reqapproval_init1.start_wf_process (itemtype                 => 'POAPPRV',

                                                     itemkey                  => NULL, -- xv_item_key,

                                                     workflowprocess          => NULL , -- 'PO_AME_APPRV_TOP',

                                                     actionoriginatedfrom     => 'PO_FORM',

                                                     documentid               => po_details_rec.po_header_id,

                                                     documentnumber           => po_details_rec.segment1,

                                                     preparerid               => po_details_rec.agent_id,

                                                     documenttypecode         => po_details_rec.document_type_code,

                                                     documentsubtype          => po_details_rec.document_subtype,

                                                     submitteraction          => 'APPROVE',

                                                     forwardtoid              => NULL,

                                                     forwardfromid            => NULL,

                                                     defaultapprovalpathid    => po_details_rec.default_approval_path_id,  -- NULL,

                                                     note                     => NULL,

                                                     printflag                => 'N',

                                                     faxflag                  => 'N',

                                                     faxnumber                => NULL,

                                                     emailflag                => 'N',

                                                     emailaddress             => NULL,

                                                     createsourcingrule       => 'N',

                                                     releasegenmethod         => 'N',

                                                     updatesourcingrule       => 'N',

                                                     massupdatereleases       => 'N',

                                                     retroactivepricechange   => 'N',

                                                     orgassignchange          => 'N',

                                                     communicatepricechange   => 'N',

                                                     p_background_flag        => 'N',

                                                     p_initiator              => NULL,

                                                     p_xml_flag               => NULL,

                                                     fpdsngflag               => 'N',

                                                     p_source_type_code       => NULL);

       fnd_file.put_line(fnd_file.log,'The PO is Approved Now =>' || po_details_rec.segment1);

       fnd_file.put_line(fnd_file.output,'The PO Approved is =>' || po_details_rec.segment1);

      END LOOP;

                COMMIT;

EXCEPTION

WHEN OTHERS THEN

 fnd_file.put_line(fnd_file.log,'Error Message: '||sqlcode||', '||SQLERRM);

--DBMS_OUTPUT.PUT_LINE('Error Message: '||sqlcode||', '||SQLERRM);

END XXXX_CALLING_POAPPRV;

END XXXX_PO_WF_SCHEDULING_PKG;

/SHOW ERRORS;

Query to find request set and its responsibility

 

SELECT FA.application_name,

       fr.responsibility_name program_attached_to,

       frg.request_group_name,

       fcp.request_set_name,

       fcp.user_request_set_name

  FROM apps.fnd_responsibility_vl   fr,

       apps.fnd_request_groups      frg,

       apps.fnd_request_group_units frgu,

       apps.fnd_request_Sets_vl     fcp,

       apps.fnd_application_vl      FA

 WHERE frg.request_group_id = fr.request_group_id

   AND frgu.request_group_id = frg.request_group_id

   AND fcp.request_set_id = frgu.request_unit_id

   AND fcp.application_id = FA.application_id

   AND upper(fcp.user_request_set_name) LIKE  UPPER(:P_REQUEST_SET_NAME);

How to delete FND MESSAGES from back end in Oracle Apps

    

FND_NEW_MESSAGES_PKG.DELETE_ROW (X_APPLICATION_ID   => 0,

                                    X_LANGUAGE_CODE    => 'US',

                                    X_MESSAGE_NAME     => 'XX_EAM_PRIORITY_TIP_MSG');

Move Order details query in Oracle Apps R12

 

select mtrh.request_number,
mtrl.FROM_SUBINVENTORY_CODE,
mtrl.TO_SUBINVENTORY_CODE,
mtrl.QUANTITY Move_Order_Qty,
mtrl.inventory_item_id,
mtrl.organization_id,
MFG.MEANING MOVE_ORDER_TYPE_NAME ,
msib.planner_code reference_type,
msib.description,
misi.secondary_inventory,
misi.attribute1 WIP_loc,
mtrl.TRANSACTION_TYPE_ID,
moq.locator_id,
mil.concatenated_segments SOURCE_LOCATOR,
(select mil1.concatenated_segments
from mtl_item_locations_KFV mil1
where  msib.organization_id = mil1.organization_id
AND moq.locator_id = mil1.inventory_location_id(+)
AND mtrl.TO_SUBINVENTORY_CODE = mil1.subinventory_code) to_locator, 
moq.primary_transaction_quantity on_hand_quantity
from mtl_txn_request_headers mtrh,
mtl_txn_request_lines mtrl,
mtl_system_items_b msib,
mtl_item_sub_inventories misi,
MFG_LOOKUPS MFG,
mtl_onhand_quantities_detail moq,
mtl_item_locations_KFV mil
where mtrh.header_id=mtrl.header_id
AND mtrh.request_number = NVL(:P_MOVE_ORDER_NO,mtrh.request_number)
AND mtrl.organization_id=msib.organization_id
AND mtrl.inventory_item_id=msib.inventory_item_id
AND msib.inventory_item_id=misi.inventory_item_id
AND msib.organization_id=misi.organization_id
AND MFG.LOOKUP_TYPE = 'MOVE_ORDER_TYPE'
AND MFG.LOOKUP_CODE = MTRH.MOVE_ORDER_TYPE
AND msib.organization_id = moq.organization_id
AND msib.inventory_item_id = moq.inventory_item_id
AND msib.organization_id = mil.organization_id
AND moq.locator_id = mil.inventory_location_id(+)
AND mtrl.FROM_SUBINVENTORY_CODE = mil.subinventory_code
AND MFG.MEANING = :P_MOVE_ORDER_TYPE
				AND mtrl.FROM_SUBINVENTORY_CODE = NVL(:P_SOURCE_SUB_INV , mtrl.FROM_SUBINVENTORY_CODE)
				AND mtrl.TO_SUBINVENTORY_CODE = NVL(:P_TO_SUB_INV , MTRL.TO_SUBINVENTORY_CODE)
				AND mtrl.organization_id = :P_INV_ORG
				AND msib.segment1 = NVL(:P_ITEM , msib.segment1)
				AND msib.planner_code = NVL(:P_REFERENCE_TYPE, msib.planner_code)

tags for highlighting a column output with colours in XML report Oracle Apps R12

 

<?if:C='R'?><xsl:attribute xdofo:ctx="block" name="background-color">red</xsl:attribute><?D?><?end if?>

<?if:C='Y'?><xsl:attribute xdofo:ctx="block" name="background-color">yellow</xsl:attribute><?D?><?end if?>

<?if:C='G'?><xsl:attribute xdofo:ctx="block" name="background-color">green</xsl:attribute><?D?><?end if?>

Supplier certificate report query in Oracle Apps R12

 

SELECT  PSPE.PARTY_ID    PARTY_ID

       ,PSPE.C_EXT_ATTR1 CERTIFICATE

       ,PSPE.C_EXT_ATTR2 CERTIFICATE_NUMBER

       ,to_char(to_date(PSPE.D_EXT_ATTR3,'DD-MM-YY'),'DD-MON-YYYY') VALID_FROM

       ,to_char(to_date(PSPE.D_EXT_ATTR4,'DD-MM-YY'),'DD-MON-YYYY') VALID_THROUGH

       ,to_char(to_date(PSPE.D_EXT_ATTR5,'DD-MM-YY'),'DD-MON-YYYY') LAST_VALIDATED

       ,PSPE.REQUEST_ID  REQUEST_ID

       ,AS1.VENDOR_NAME  VENDOR_NAME

       ,AS1.SEGMENT1     VENDOR_NUMBER

       ,(-1 * FLOOR((SYSDATE - to_date(PSPE.D_EXT_ATTR4,'DD-MM-YY')))) D

       ,CASE WHEN (-1 * FLOOR((SYSDATE - to_date(PSPE.D_EXT_ATTR4,'DD-MM-YY')))) < 10 THEN 'R'

             WHEN (-1 * FLOOR((SYSDATE - to_date(PSPE.D_EXT_ATTR4,'DD-MM-YY')))) BETWEEN 10 AND 15 THEN 'Y'

             ELSE 'G' END C

FROM POS_SUPP_PROF_EXT_B PSPE 

    ,AP_SUPPLIERS AS1

WHERE 1 = 1

  AND PSPE.attr_group_id=221 

  AND to_date(PSPE.D_EXT_ATTR4,'DD-MM-YY') BETWEEN SYSDATE and SYSDATE+:P_EXP_DAYS

  AND AS1.PARTY_ID = PSPE.PARTY_ID

ORDER BY TO_DATE(PSPE.D_EXT_ATTR4,'DD-MM-YY')

        ,AS1.VENDOR_NAME

How to close Aged PO's through API in Oracle Apps R12

 

create or replace PACKAGE BODY   XX_AGEDPO_CLOSE_PKG

AS

PROCEDURE main (p_errbuf                 OUT VARCHAR2,

                p_retcode                OUT NUMBER,

                P_ORG_ID IN  NUMBER,

                p_po_age                 IN  NUMBER,

                p_catalog                IN  VARCHAR2,

                p_facilities             IN  VARCHAR2)

IS

  

CURSOR lcu_aged_pos

IS

    SELECT    aps.vendor_name,

                aps.segment1 vendor_num,

                poh.segment1 PO_Num,

                poh.po_header_id

poh.org_id

      FROM      po_headers_all poh,

                po_lines_all pol,

                po_line_locations_all poll,

                po_distributions_all pod,

                ap_suppliers aps,

                gl_code_combinations gcc

     where      poh.po_header_id = pol.po_header_id

            and pol.item_id is NOT NULL

                and pol.po_line_id = poll.po_line_id

                and poll.line_location_id = pod.line_location_id

                and poh.vendor_id = aps.vendor_id

                and pod.code_combination_id = gcc.code_combination_id

                and nvl(aps.hold_flag, 'N') = 'N'

                and nvl(poh.cancel_flag, 'N') = 'N'

                and poh.type_lookup_code = 'STANDARD'

                and poh.authorization_status = 'APPROVED'

                and nvl(pol.cancel_flag, 'N') = 'N'

                and nvl(poh.closed_code, 'OPEN') = 'OPEN'

                and nvl(poll.closed_code, 'OPEN') IN ( 'OPEN','CLOSED FOR RECEIVING','CLOSED FOR INVOICE')

                and not exists (select i.po_header_id

                                from ap_invoices_all i,ap_holds h

                                where i.po_header_id = poh.po_header_id

                                and i.invoice_id = h.invoice_id

                                and h.release_lookup_code IS NULL)

                and poh.org_id= :P_ORG_ID           

                and trunc(poll.need_by_date) < sysdate - :p_po_age;

     group by   aps.vendor_name,

                aps.segment1,

                poh.segment1,

                poh.po_header_id

     order by   aps.vendor_name,poh.segment1;

 

   l_sql             VARCHAR2 (32767);

   l_success_count   NUMBER := 0;

   l_error_count     NUMBER := 0;

   lv_result         BOOLEAN;

   lv_return_code    VARCHAR2(20);

   g_user_id         NUMBER  :=  FND_GLOBAL.USER_ID;

   g_resp_id         NUMBER  :=  FND_GLOBAL.RESP_ID;

   g_resp_appl_id    NUMBER  :=  FND_GLOBAL.RESP_APPL_ID;

BEGIN

      Fnd_Global.apps_initialize(g_user_id,

                                 g_resp_id,

                                 g_resp_appl_id);

FOR rec_aged_pos IN lcu_aged_pos LOOP

      BEGIN

       lv_result :=    PO_ACTIONS.main(

          P_DOCID => rec_aged_pos.po_header_id,

          P_DOCTYP => 'PO',

          P_DOCSUBTYP => 'STANDARD',

          P_LINEID => NULL,

          P_SHIPID => NULL,

          P_ACTION => 'CLOSE',

          P_REASON => 'Close Aged Purchase Order ',

          P_CALLING_MODE => 'PO',

          P_CONC_FLAG => 'N',

          P_RETURN_CODE => lv_return_code,

          P_AUTO_CLOSE => 'N',

          P_ACTION_DATE => sysdate,

          P_ORIGIN_DOC_ID => NULL );

       IF lv_return_code IS NULL THEN

           FND_FILE.put_line(fnd_file.output,'PO_NUM:'||rec_aged_pos.PO_Num||',        Org_Id:'||rec_aged_pos.org_id);

           l_success_count := l_success_count + 1;

        ELSE 

           FND_FILE.put_line(fnd_file.log,'PO_HEADER_ID: '||rec_aged_pos.po_header_id|| ', error message: '||lv_return_code);

           l_error_count := l_error_count + 1;

       END IF;

       COMMIT;

       END;

     END LOOP;

    FND_FILE.PUT_LINE ( FND_FILE.log,'Total POs closed: '||l_success_count );

    FND_FILE.PUT_LINE ( FND_FILE.log,'Total POs failed: '||l_error_count );

EXCEPTION

      WHEN OTHERS THEN

      FND_FILE.PUT_LINE ( FND_FILE.LOG,'Error in main procedure '||SQLERRM);

      p_retcode := 2;

      p_errbuf  := 'Error in main procedure :'||SQLERRM;

END main;

END XX_AGEDPO_CLOSE_PKG;

/

SHOW ERRORS;

How to update Vendor/ Supplier site details by using API

create or replace PACKAGE apps.XX_UPDATE_VEN_SITE_PKG
IS

   --|===========================================================================|
   --|                       TouchTunes                                          |
   --|                                                                           |
   --| Description    : This package is used to update the supplier site details |
   --|                  as per the business request, for detailed information    |
   --|                  please refer the JIRA ticket number #XXXX-8.             |
   --|                                                                           |
   --| Program Name   : XX_UPDATE_VEN_SITE_PKG                                   |
   --| Module Name    : AP                                                       |
   --|                                                                           |
   --| Modification History:                                                     |
   --| Name               DATE         Description                  Version      |
   --| ---------------    ----------   -------------------------    --------     |
   --| XXXXXXXXXXX        20-Jan-20    Created initial version      V1.0         |
   --|===========================================================================|

PROCEDURE UPDATE_VENDOR_SITE_DET (ERRBUF OUT VARCHAR2
                                 , RETCODE OUT VARCHAR2
                                 , P_ORG_ID IN NUMBER);

END XX_UPDATE_VEN_SITE_PKG;
/


create or replace PACKAGE BODY apps.XX_UPDATE_VEN_SITE_PKG
IS

   --|===========================================================================|
   --|                       TouchTunes                                          |
   --|                                                                           |
   --| Description    : This package is used to update the supplier site details |
   --|                  as per the business request, for detailed information    |
   --|                  please refer the JIRA ticket number #XXXXX-8.            |
   --|                                                                           |
   --| Program Name   : XX_UPDATE_VEN_SITE_PKG                                   |
   --| Module Name    : AP                                                       |
   --|                                                                           |
   --| Modification History:                                                     |
   --| Name               DATE         Description                  Version      |
   --| ---------------    ----------   -------------------------    --------     |
   --| XXXXXXXXXX         20-Jan-20    Created initial version      V1.0         |
   --|===========================================================================|

PROCEDURE UPDATE_VENDOR_SITE_DET (ERRBUF OUT VARCHAR2
                                 , RETCODE OUT VARCHAR2
                                 , P_ORG_ID IN NUMBER)
IS

l_vendor_site_rec ap_vendor_pub_pkg.r_vendor_site_rec_type;
l_return_status     VARCHAR2(10);
l_msg_count     NUMBER;
l_msg_data  VARCHAR2(1000);
l_vendor_site_id    NUMBER;
l_party_site_id     NUMBER;
l_location_id   NUMBER;

CURSOR lcu_sup_site_det
IS
SELECT
APS.vendor_id ,
-- APS.vendor_name "Supplier Name" ,
--APS.segment1 "Supplier Num" ,
APSS.VENDOR_SITE_ID ,
APSS.vendor_site_code vendor_site_code ,
APSS.ADDRESS_LINE1,
APSS.COUNTRY,
APSS.ORG_ID,
APSS.PURCHASING_SITE_FLAG,
APSS.RFQ_ONLY_SITE_FLAG,
APSS.PAY_SITE_FLAG,
--hou.name "Operating Unit Name" ,
(SELECT NAME FROM HR_OPERATING_UNITS WHERE ORGANIZATION_ID = APSS.ORG_ID ) NAME,
--APSS.PARTY_SITE_ID,
DECODE( P_ORG_ID , 261 ,41498, 102,19389, 262, 41471,  APSS.SHIP_TO_LOCATION_ID)  SHIP_TO_LOCATION_ID,
DECODE( P_ORG_ID , 261 ,41498, 102,19389, 262, 41471,  APSS.BILL_TO_LOCATION_ID) BILL_TO_LOCATION_ID,
-- BILL_TO.LOCATION_CODE "BILL TO LOC",
-- SHIP_TO.LOCATION_CODE "SHIP TO LOC",
-- apss.ship_via_lookup_code "Ship Via",
--APSS.FREIGHT_TERMS_LOOKUP_CODE FREIGHT
DECODE( P_ORG_ID , 261 , 'UPS' ,apss.ship_via_lookup_code)  ship_via_lookup_code ,
DECODE( P_ORG_ID , 261 , 'UPS GROUND' , APSS.FREIGHT_TERMS_LOOKUP_CODE) FREIGHT_TERMS_LOOKUP_CODE
FROM APPS.HR_EMPLOYEES HE,
APPS.HR_LOCATIONS_V SHIP_TO,
APPS.HR_LOCATIONS_V BILL_TO,
apps.AP_SUPPLIER_SITES_ALL APSS,
ap.AP_SUPPLIERS APS,
apps.hz_parties hp
WHERE HE.EMPLOYEE_ID(+) = APS.EMPLOYEE_ID
and aps.party_id=hp.party_id
AND SHIP_TO.LOCATION_ID(+) = APSS.SHIP_TO_LOCATION_ID
AND BILL_TO.LOCATION_ID = APSS.BILL_TO_LOCATION_ID
AND NVL(APSS.INACTIVE_DATE,SYSDATE+1) >= SYSDATE
AND APS.VENDOR_ID = APSS.VENDOR_ID
AND NVL(APS.END_DATE_ACTIVE,SYSDATE+1) >= SYSDATE
AND NVL(APS.ENABLED_FLAG,'Y') = 'Y'
AND APSS.ORG_ID = P_ORG_ID
--AND APSS.VENDOR_ID = 39 -- 546934 -- 547920
--AND BILL_TO.LOCATION_CODE = 'XXXXXX'
ORDER BY APS.vendor_id ,
          APSS.VENDOR_SITE_ID ,
          APSS.vendor_site_code;

BEGIN

FOR rec_sup_site_det IN lcu_sup_site_det LOOP
--Required
l_vendor_site_rec.vendor_id                 := rec_sup_site_det.vendor_id  ;
l_vendor_site_rec.VENDOR_SITE_ID            := rec_sup_site_det.VENDOR_SITE_ID ;
l_vendor_site_rec.vendor_site_code          := rec_sup_site_det.vendor_site_code   ;
l_vendor_site_rec.address_line1             := rec_sup_site_det.address_line1 ; --
l_vendor_site_rec.country                   := rec_sup_site_det.country ;
l_vendor_site_rec.org_id                    := rec_sup_site_det.org_id  ;
l_vendor_site_rec.purchasing_site_flag      :=  rec_sup_site_det.purchasing_site_flag ;
l_vendor_site_rec.pay_site_flag             := rec_sup_site_det.pay_site_flag ;
l_vendor_site_rec.rfq_only_site_flag        := rec_sup_site_det.rfq_only_site_flag  ;
l_vendor_site_rec.SHIP_TO_LOCATION_ID       := rec_sup_site_det.SHIP_TO_LOCATION_ID  ;
l_vendor_site_rec.BILL_TO_LOCATION_ID       := rec_sup_site_det.BILL_TO_LOCATION_ID  ;
l_vendor_site_rec.SHIP_VIA_LOOKUP_CODE      := rec_sup_site_det.SHIP_VIA_LOOKUP_CODE  ;
l_vendor_site_rec.FREIGHT_TERMS_LOOKUP_CODE := rec_sup_site_det.FREIGHT_TERMS_LOOKUP_CODE  ;

--pos_vendor_pub_pkg.create_vendor_site
--(
--p_vendor_site_rec => l_vendor_site_rec,
--x_return_status => l_return_status,
--x_msg_count => l_msg_count,
--x_msg_data => l_msg_data,
--x_vendor_site_id => l_vendor_site_id,
--x_party_site_id => l_party_site_id,
--x_location_id => l_location_id
--);

pos_vendor_pub_pkg.Update_Vendor_Site(p_vendor_site_rec => l_vendor_site_rec ,
                                      x_return_status  => l_return_status,
                                      x_msg_count      => l_msg_count,
                                      x_msg_data       => l_msg_data);

                                      if l_return_status = 'S' then
                                        dbms_output.put_line('Vendor Id: '||l_vendor_site_rec.vendor_id || ', Vendor Site Code: '||l_vendor_site_rec.vendor_site_code || ' , Org Name '|| rec_sup_site_det.NAME|| ', Ship to  and Bill to: '||l_vendor_site_rec.SHIP_TO_LOCATION_ID);
                                        fnd_file.put_line(fnd_file.output, 'Vendor Id: '||l_vendor_site_rec.vendor_id || ', Vendor Site Code: '||l_vendor_site_rec.vendor_site_code || ' , Org Name '|| rec_sup_site_det.NAME|| ', Ship to  and Bill to: '||l_vendor_site_rec.SHIP_TO_LOCATION_ID );
                                       -- dbms_output.put_line('Vendor Id: '||l_vendor_site_rec.vendor_id || ', Vendor Site Code: '||l_vendor_site_rec.vendor_site_code || ' , Org Name '|| rec_sup_site_det.NAME);
                                       -- fnd_file.put_line(fnd_file.output, 'Vendor Id: '||l_vendor_site_rec.vendor_id || ', Vendor Site Code: '||l_vendor_site_rec.vendor_site_code || ' , Org Name '|| rec_sup_site_det.NAME );
                                      else
                                     -- if l_return_status <> 'S' then
                                       dbms_output.put_line('Update failed for Vendor Id: '||l_vendor_site_rec.vendor_id || ', Vendor Site Code: '||l_vendor_site_rec.vendor_site_code || ' , Org Name '|| rec_sup_site_det.NAME);
                                       fnd_file.put_line(fnd_file.log, 'Update failed for Vendor Id: '||l_vendor_site_rec.vendor_id || ', Vendor Site Code: '||l_vendor_site_rec.vendor_site_code || ' , Org Name '|| rec_sup_site_det.NAME );
                                       dbms_output.put_line(' Error Message: '|| l_msg_data );
                                       fnd_file.put_line(fnd_file.log, ' Error Message: '|| l_msg_data );
                                      end if;

END LOOP;

COMMIT;

EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line ('Error Message: '|| SQLCODE || ' , '|| SQLERRM );
  fnd_file.put_line( fnd_file.log ,'Error Message: '|| SQLCODE || ' , '|| SQLERRM );

END UPDATE_VENDOR_SITE_DET;

END XX_UPDATE_VEN_SITE_PKG;
/

Supplier certification report in R12

In front end we can see the supplier certifications by following the below navigation

Navigation : Supplier Lifecycle Management –> Search for Supplier "%XX%" –> Click on the            Organization tab -> Click on Supplier Certification Tab

Query:
--------

SELECT  PSPE.PARTY_ID    PARTY_ID
       ,PSPE.C_EXT_ATTR1 CERTIFICATE
       ,PSPE.C_EXT_ATTR2 CERTIFICATE_NUMBER
       ,to_char(to_date(PSPE.D_EXT_ATTR3,'DD-MM-YY'),'DD-MON-YYYY') VALID_FROM
       ,to_char(to_date(PSPE.D_EXT_ATTR4,'DD-MM-YY'),'DD-MON-YYYY') VALID_THROUGH
       ,to_char(to_date(PSPE.D_EXT_ATTR5,'DD-MM-YY'),'DD-MON-YYYY') LAST_VALIDATED
       ,PSPE.REQUEST_ID  REQUEST_ID
       ,AS1.VENDOR_NAME  VENDOR_NAME
       ,AS1.SEGMENT1     VENDOR_NUMBER
FROM POS_SUPP_PROF_EXT_B PSPE
    ,AP_SUPPLIERS AS1
WHERE 1 = 1
  AND PSPE.attr_group_id=221
  AND to_date(PSPE.D_EXT_ATTR4,'DD-MM-YY') BETWEEN SYSDATE and SYSDATE+:P_EXP_DAYS
  AND AS1.PARTY_ID = PSPE.PARTY_ID
ORDER BY TO_DATE(PSPE.D_EXT_ATTR4,'DD-MM-YY')
        ,AS1.VENDOR_NAME

FNDLOAD scripts in R12

1. Concurrent Program
---------------------

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CUSTOM_CP.ldt PROGRAM APPLICATION_SHORT_NAME="XXCUST" CONCURRENT_PROGRAM_NAME="XX_CONCURRENT_PROGRAM"

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CUSTOM_CP.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE


2. Profile
----------

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct XX_CUSTOM_PRF.ldt PROFILE PROFILE_NAME="XX_PROFILE_NAME" APPLICATION_SHORT_NAME="XXCUST"

$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct XX_CUSTOM_PRF.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE


3. Lookups
----------

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct XX_CUSTOM_LKP.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="XXCUST" LOOKUP_TYPE="XX_LOOKUP_TYPE"

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct XX_CUSTOM_LKP.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE



4. Request Set
--------------

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XX_CUSTOM_RS.ldt REQ_SET REQUEST_SET_NAME='REQUEST_SET_NAME'

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct  XX_CUSTOM_RS.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE



5. FND Message
--------------

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_CUSTOM_MESG.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME="XXCUST" MESSAGE_NAME="MESSAGE_NAME%"

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_CUSTOM_MESG.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE



6. D2K FORMS
------------

$FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XX_CUSTOM_FRM.ldt FORM FORM_NAME="FORM_NAME"
     
$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XX_CUSTOM_FRM.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE



7. Form Function
----------------

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XX_CUSTOM_FUNC.ldt FUNCTION FUNCTION_NAME="FORM_FUNCTION_NAME"

$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XX_CUSTOM_FUNC.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE



8. Alerts
---------

FNDLOAD apps/apps 0 Y DOWNLOAD $ALR_TOP/patch/115/import/alr.lct XX_CUSTOM_ALR.ldt ALR_ALERTS APPLICATION_SHORT_NAME=XXCUST ALERT_NAME="XX - Alert Name"

FNDLOAD apps/apps 0 Y UPLOAD $ALR_TOP/patch/115/import/alr.lct XX_CUSTOM_ALR.ldt CUSTOM_MODE=FORCE



9. Value Set
------------

$FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_CUSTOM_VS.ldt VALUE_SET FLEX_VALUE_SET_NAME="XX Value Set Name"

$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct XX_CUSTOM_VS.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE



10. Data Definition and Associated Template
-------------------------------------------

FNDLOAD apps/$CLIENT_APPS_PWD O Y DOWNLOAD  $XDO_TOP/patch/115/import/xdotmpl.lct XX_CUSTOM_DD.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME='XXCUST' DATA_SOURCE_CODE='XX_SOURCE_CODE' TMPL_APP_SHORT_NAME='XXCUST' TEMPLATE_CODE='XX_SOURCE_CODE'

FNDLOAD apps/$CLIENT_APPS_PWD O Y UPLOAD $XDO_TOP/patch/115/import/xdotmpl.lct XX_CUSTOM_DD.ldt



11. DATA_TEMPLATE (Data Source .xml file)
-----------------------------------------

java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD -DB_USERNAME apps -DB_PASSWORD apps -JDBC_CONNECTION '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XX_HOST_NAME)(PORT=XX_PORT_NUMBER))(CONNECT_DATA=(SERVICE_NAME=XX_SERVICE_NAME)))' -LOB_TYPE DATA_TEMPLATE -LOB_CODE XX_TEMPLATE -APPS_SHORT_NAME XXCUST -LANGUAGE en -lct_FILE $XDO_TOP/patch/115/import/xdotmpl.lct -LOG_FILE $LOG_FILE_NAME

java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME apps -DB_PASSWORD apps -JDBC_CONNECTION '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XX_HOST_NAME)(PORT=XX_PORT_NUMBER))(CONNECT_DATA=(SERVICE_NAME=XX_SERVICE_NAME)))' -LOB_TYPE DATA_TEMPLATE -LOB_CODE XX_TEMPLATE -XDO_FILE_TYPE XML -FILE_NAME $DATA_FILE_PATH/$DATA_FILE_NAME.xml -APPS_SHORT_NAME XXCUST -NLS_LANG en -TERRITORY US -LOG_FILE $LOG_FILE_NAME



12. RTF TEMPLATE (Report Layout .rtf file)
------------------------------------------

java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD -DB_USERNAME apps -DB_PASSWORD apps -JDBC_CONNECTION '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XX_HOST_NAME)(PORT=XX_PORT_NUMBER))(CONNECT_DATA=(SERVICE_NAME=XX_SERVICE_NAME)))' -LOB_TYPE TEMPLATE -LOB_CODE XX_TEMPLATE -APPS_SHORT_NAME XXCUST -LANGUAGE en -TERRITORY US -lct_FILE $XDO_TOP/patch/115/import/xdotmpl.lct -LOG_FILE $LOG_FILE_NAME

java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME apps -DB_PASSWORD apps -JDBC_CONNECTION '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XX_HOST_NAME)(PORT=XX_PORT_NUMBER))(CONNECT_DATA=(SERVICE_NAME=SERVICE_NAME)))' -LOB_TYPE TEMPLATE -LOB_CODE XX_TEMPLATE -XDO_FILE_TYPE RTF -FILE_NAME $RTF_FILE_PATH/$RTF_FILE_NAME.rtf -APPS_SHORT_NAME XXCUST -NLS_LANG en -TERRITORY US -LOG_FILE $LOG_FILE_NAME

Namespace prefix ‘ref’ used but not declared in XML Publisher reports

Solution: This error will occur bevause of the higher version of the BI Publisher.

steps to resolve the error.

Step1: Open the rtf file, click on BI Publisher tab -> Options -> click on Build tab
-> set Form field size as "Backward Compatible" 

Step2: remove all the fields on rtf template and save then insert the fields and upload the rtf template. you will not
get the error called "Namespace prefix ‘ref’ used but not declared in XML Publisher"

How to find the request group name based on Concurrent program ?

SELECT cpt.user_concurrent_program_name     "Concurrent Program Name",
       DECODE(rgu.request_unit_type,
              'P', 'Program',
              'S', 'Set',
              rgu.request_unit_type)        "Unit Type",
       cp.concurrent_program_name           "Concurrent Program Short Name",
       rg.application_id                    "Application ID",
       rg.request_group_name                "Request Group Name",
       fat.application_name                 "Application Name",
       fa.application_short_name            "Application Short Name",
       fa.basepath                          "Basepath"
  FROM fnd_request_groups          rg,
       fnd_request_group_units     rgu,
       fnd_concurrent_programs     cp,
       fnd_concurrent_programs_tl  cpt,
       fnd_application             fa,
       fnd_application_tl          fat
 WHERE rg.request_group_id       =  rgu.request_group_id
   AND rgu.request_unit_id       =  cp.concurrent_program_id
   AND cp.concurrent_program_id  =  cpt.concurrent_program_id
   AND rg.application_id         =  fat.application_id
   AND fa.application_id         =  fat.application_id
   AND cpt.language              =  USERENV('LANG')
   AND fat.language              =  USERENV('LANG')
   AND cpt.user_concurrent_program_name = '%AP Vendor Audit Report%';

Open PO details query in Oracle Apps R12

--Open PO query.
SELECT
    pha.segment1 ponumber,
    hou.name organization_code,
    pha.type_lookup_code potype,
    trunc(pha.creation_date) cdate,
    pv.vendor_name supplier,
    pv.segment1 supplier_number,
    pvs.vendor_site_code suppliersite,
    hl1.location_code shipto_loc,
    hl2.location_code billto_loc,
    (SELECT pla1.quantity * pla1.unit_price
     FROM po_lines_all pla1
     WHERE 1=1
     AND pla1.po_line_id = pla.po_line_id) PO_LINE_AMT ,
    pha.currency_code currency,
    papf.full_name buyer,
    pha.authorization_status,
    pha.comments comments,
    atl.name terms,
    plla.need_by_date,
    plla.promised_date,
    pha.approved_date,
    pha.closed_code
FROM
    apps.po_headers_all pha,
    apps.ap_suppliers pv,
    apps.ap_supplier_sites_all pvs,
    hr_locations hl1,
    hr_locations hl2,
    apps.per_all_people_f papf,
    apps.po_lines_all pla,
    hr_operating_units hou,
    apps.ap_terms_tl atl,
    apps.po_line_locations_all plla
WHERE
        pha.vendor_id = pv.vendor_id
    AND pha.type_lookup_code      NOT IN ('RFQ','QUOTATION')
    AND pha.vendor_site_id = pvs.vendor_site_id
    AND pha.ship_to_location_id = hl1.location_id
    AND pha.bill_to_location_id = hl2.location_id
    AND pha.agent_id = papf.person_id
    AND pha.po_header_id = pla.po_header_id
    AND pha.org_id = hou.organization_id
    AND pha.terms_id = atl.term_id
    AND plla.po_header_id = pha.po_header_id
    AND pla.po_line_id = plla.po_line_id
    AND pha.org_id = plla.org_id
    AND pha.closed_code = 'OPEN'
    AND pha.org_id = 7891
    AND pha.authorization_status = 'APPROVED'
ORDER BY 1

Open sales order details query in Oracle Apps R12

-- Open SO.

SELECT ooh.order_number
      ,ooh.org_id
      ,OOH.OPEN_FLAG
      ,ool.open_flag "Lines Flag"
      ,ool.inventory_item_id
FROM  OE_ORDER_HEADERS_ALL ooh
     ,OE_ORDER_LINES_ALL ool
WHERE 1=1
AND ooh.org_id = 7891
AND ooh.header_id = ool.header_id
AND ooh.open_flag = 'Y';

Open po receipts query in Oracle Apps R12

-- Open PO Receipts.


SELECT  h.receipt_num
       ,h.shipment_header_id
       ,l.SHIPMENT_LINE_STATUS_CODE
       ,pha.segment1 PO_NUM
       ,PHA.po_header_id
FROM  rcv_shipment_headers h
      ,rcv_shipment_lines l
      ,po_headers_all pha
WHERE h.shipment_header_id = l.shipment_header_id
AND l.source_document_code = 'PO'
AND pha.type_lookup_code      NOT IN ('RFQ','QUOTATION')
AND pha.po_header_id  = L.PO_HEADER_ID
AND L.SHIPMENT_LINE_STATUS_CODE not in  ('FULLY RECEIVED');

Outbound interface by using UTL_FILE api in Oracle Apps R12?

-- This is package is used to take the backup of the PLSQL objects.

create or replace PACKAGE XX_WRITE_FILES_PKG
IS

PROCEDURE XX_WRITE_FILES_PRC(P_OBJECT_NAME IN VARCHAR2);

PROCEDURE MAIN;

END XX_WRITE_FILES_PKG;
/
SHO ERRORS
/


create or replace PACKAGE BODY XX_WRITE_FILES_PKG
IS

-- |                                                                             |
-- |Description      : XX_WRITE_FILES_PKG is used to take the backup of          |
-- |                   Database objects like PROCEDURE,PACKAGE BODY,PACKAGE      |
-- |                   TYPE BODY,TRIGGER,FUNCTION,TYPE.                          |

PROCEDURE Debug( p_message  IN  VARCHAR2
               ) IS
lv_message       VARCHAR2(200);

BEGIN

      lv_message    := SUBSTR(p_message,1,240);
       fnd_file.put_line(fnd_file.log, lv_message);
  -- dbms_output.put_line(lv_message);

END Debug;


-- +====================================================================+
-- | Name             : write_out                                       |
-- | Description      : To write to the Output file of a concurrent Prog|
-- | Parameters       : pv_mesg            - Message String             |
-- |                                                                    |
-- +====================================================================+
PROCEDURE write_out(pv_mesg  IN  VARCHAR2) IS
BEGIN

    FND_FILE.PUT_LINE( FND_FILE.OUTPUT, substr(pv_mesg,1,500));
   --  dbms_output.put_line(substr(pv_mesg,1,500));

END write_out;


PROCEDURE XX_WRITE_FILES_PRC (P_OBJECT_NAME IN VARCHAR2)
IS

CURSOR lcu_file_name (cv_object_name VARCHAR2)
IS
SELECT text
FROM   user_source
WHERE NAME = cv_object_name;

l_file  UTL_FILE.FILE_TYPE;

BEGIN

Debug('XX_WRITE_FILES_PRC => Begining of Procedure');

l_file := UTL_FILE.FOPEN('/usr/tmp',P_OBJECT_NAME||'.TXT','W');

Debug('XX_WRITE_FILES_PRC => After opening file '||P_OBJECT_NAME||'.TXT');
FOR lr_file_name_rec IN lcu_file_name(P_OBJECT_NAME) LOOP

UTL_FILE.PUT_LINE(l_file,lr_file_name_rec.TEXT);
END LOOP;
Debug('XX_WRITE_FILES_PRC => After closing the for-loop lr_file_name_rec');

UTL_FILE.FCLOSE(l_file);
Debug('XX_WRITE_FILES_PRC => End of procedure');

EXCEPTION
WHEN OTHERS THEN
Debug('XX_WRITE_FILES_PRC error at processing backup file for object '||P_OBJECT_NAME);
Debug('XX_WRITE_FILES_PRC => Error: '||SQLCODE ||','||SQLERRM);
END XX_WRITE_FILES_PRC;


PROCEDURE MAIN (ERRBUF  OUT VARCHAR2
               ,RETCODE OUT VARCHAR2)
IS

CURSOR lcu_object_name
IS
SELECT  xbon.OBJECT_NAME
FROM    XX_BKUP_OBJECT_NAMES xbon;

BEGIN

Debug('MAIN => Begining of PROCEDURE');


FOR lr_object_name IN lcu_object_name LOOP
Debug('MAIN => Entered into for-loop lr_object_name');

XX_WRITE_FILES_PRC(lr_object_name.OBJECT_NAME);
write_out('Backup file created for object '||lr_object_name.OBJECT_NAME);
END LOOP;
Debug('MAIN => End of PROCEDURE');

EXCEPTION
WHEN OTHERS THEN
Debug('MAIN => Error: '||SQLCODE ||','||SQLERRM);
END MAIN;

END XX_WRITE_FILES_PKG;
/
SHO ERRORS
/

How to take backup of PLSQL objects programatically in Oracle Apps R12 ?

This is package is used to take the backup of the PLSQL objects.

create or replace PACKAGE XX_WRITE_FILES_PKG
IS

PROCEDURE XX_WRITE_FILES_PRC(P_OBJECT_NAME IN VARCHAR2);

PROCEDURE MAIN;

END XX_WRITE_FILES_PKG;
/
SHO ERRORS
/


create or replace PACKAGE BODY XX_WRITE_FILES_PKG
IS

-- |                                                                             |
-- |Description      : XX_WRITE_FILES_PKG is used to take the backup of          |
-- |                   Database objects like PROCEDURE,PACKAGE BODY,PACKAGE      |
-- |                   TYPE BODY,TRIGGER,FUNCTION,TYPE.                          |

PROCEDURE Debug( p_message  IN  VARCHAR2
               ) IS
lv_message       VARCHAR2(200);

BEGIN

      lv_message    := SUBSTR(p_message,1,240);
       fnd_file.put_line(fnd_file.log, lv_message);
  -- dbms_output.put_line(lv_message);

END Debug;


-- +====================================================================+
-- | Name             : write_out                                       |
-- | Description      : To write to the Output file of a concurrent Prog|
-- | Parameters       : pv_mesg            - Message String             |
-- |                                                                    |
-- +====================================================================+
PROCEDURE write_out(pv_mesg  IN  VARCHAR2) IS
BEGIN

    FND_FILE.PUT_LINE( FND_FILE.OUTPUT, substr(pv_mesg,1,500));
   --  dbms_output.put_line(substr(pv_mesg,1,500));

END write_out;


PROCEDURE XX_WRITE_FILES_PRC (P_OBJECT_NAME IN VARCHAR2)
IS

CURSOR lcu_file_name (cv_object_name VARCHAR2)
IS
SELECT text
FROM   user_source
WHERE NAME = cv_object_name;

l_file  UTL_FILE.FILE_TYPE;

BEGIN

Debug('XX_WRITE_FILES_PRC => Begining of Procedure');

l_file := UTL_FILE.FOPEN('/usr/tmp',P_OBJECT_NAME||'.TXT','W');

Debug('XX_WRITE_FILES_PRC => After opening file '||P_OBJECT_NAME||'.TXT');
FOR lr_file_name_rec IN lcu_file_name(P_OBJECT_NAME) LOOP

UTL_FILE.PUT_LINE(l_file,lr_file_name_rec.TEXT);
END LOOP;
Debug('XX_WRITE_FILES_PRC => After closing the for-loop lr_file_name_rec');

UTL_FILE.FCLOSE(l_file);
Debug('XX_WRITE_FILES_PRC => End of procedure');

EXCEPTION
WHEN OTHERS THEN
Debug('XX_WRITE_FILES_PRC error at processing backup file for object '||P_OBJECT_NAME);
Debug('XX_WRITE_FILES_PRC => Error: '||SQLCODE ||','||SQLERRM);
END XX_WRITE_FILES_PRC;


PROCEDURE MAIN (ERRBUF  OUT VARCHAR2
               ,RETCODE OUT VARCHAR2)
IS

CURSOR lcu_object_name
IS
SELECT  xbon.OBJECT_NAME
FROM    XX_BKUP_OBJECT_NAMES xbon;

BEGIN

Debug('MAIN => Begining of PROCEDURE');


FOR lr_object_name IN lcu_object_name LOOP
Debug('MAIN => Entered into for-loop lr_object_name');

XX_WRITE_FILES_PRC(lr_object_name.OBJECT_NAME);
write_out('Backup file created for object '||lr_object_name.OBJECT_NAME);
END LOOP;
Debug('MAIN => End of PROCEDURE');

EXCEPTION
WHEN OTHERS THEN
Debug('MAIN => Error: '||SQLCODE ||','||SQLERRM);
END MAIN;

END XX_WRITE_FILES_PKG;
/
SHO ERRORS
/

Vendor creation by using API in Orale Apps R12

DECLARE
   l_vendor_rec       ap_vendor_pub_pkg.r_vendor_rec_type;
   l_return_status   VARCHAR2(10);
   l_msg_count       NUMBER;
   l_msg_data         VARCHAR2(1000);
   l_vendor_id        NUMBER;
   l_party_id           NUMBER;
   cursor c1 is select * from xx_sup_stage;
BEGIN
   -- --------------
   -- Required
   -- --------------
   for i in c1 loop
   l_vendor_rec.VENDOR_ID:= i.VENDOR_ID;
   l_vendor_rec.VENDOR_NAME:= i.VENDOR_NAME;
   l_vendor_rec.VENDOR_NAME_ALT:=i.VENDOR_NAME_ALT;
   l_vendor_rec.SEGMENT1:= i.SEGMENT1;
   l_vendor_rec.SUMMARY_FLAG:=i.SUMMARY_FLAG;
   l_vendor_rec.ENABLED_FLAG:=i.ENABLED_FLAG;
   l_vendor_rec.TERMS_ID:=i.TERMS_ID;
   l_vendor_rec.PAY_DATE_BASIS_LOOKUP_CODE:=i.PAY_DATE_BASIS_LOOKUP_CODE;
   l_vendor_rec.PAY_GROUP_LOOKUP_CODE:=i.PAY_GROUP_LOOKUP_CODE;
   l_vendor_rec.INVOICE_CURRENCY_CODE:=i.INVOICE_CURRENCY_CODE;
   l_vendor_rec.PAYMENT_CURRENCY_CODE:=i.PAYMENT_CURRENCY_CODE;
   l_vendor_rec.START_DATE_ACTIVE:=i.START_DATE_ACTIVE;
 
   -- -------------
   -- Optional
   -- --------------
   l_vendor_rec.match_option  :='R';
 
   pos_vendor_pub_pkg.create_vendor
   (    -- -------------------------
        -- Input Parameters
        -- -------------------------
        p_vendor_rec      => l_vendor_rec,
        -- ----------------------------
        -- Output Parameters
        -- ----------------------------
        x_return_status   => l_return_status,
        x_msg_count       => l_msg_count,
        x_msg_data         => l_msg_data,
        x_vendor_id        => l_vendor_id,
        x_party_id           => l_party_id
   );
 
   IF l_return_status ='S' THEN
  -- Update vendor id in stage tables through autonomus prrogram.
 
   ELSE
   -- Update vendor id in stage tables through autonomus prrogram.
  End if;
 
  end loop;
 
  commit;
 
EXCEPTION
      WHEN OTHERS THEN
                   ROLLBACK;
                   DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

How to add the lay out to XML report when calling through form personalization in R12

In form personalization we have to do below steps, to add the layout to XML output concurrent programs.

Create a sequence 1 with type Built in and built in type as “Execute a procedure”



='declare

  lv_layout BOOLEAN;

  begin

  lv_layout:=fnd_request.add_layout(template_appl_name => '''||'XXFIN'|| '''

                                   ,template_code => '''||'FMCBR201' || '''

                                   ,template_language => '''||'en'|| '''

                                   ,template_territory => '''||'US'||'''

                                   ,output_format => '''||'PDF'||''');

commit;

end'

How to find customer credit limit amount in Oracle Apps R12

SELECT  a.overall_credit_limit
FROM   HZ_CUST_PROFILE_AMTS a
     , HZ_CUST_ACCOUNTS b
, ar_customers c
, hz_cust_site_uses_all d
, ar_payment_schedules_all e
, hz_cust_acct_sites_all f
, hz_party_sites g
WHERE    overall_credit_limit IS NOT NULL
         and a.cust_account_id = b.cust_account_id
         and b.account_number = c.customer_number
         and a.site_use_id = d.site_use_id
         and c.customer_id = e.customer_id
         --and e.STATUS <> 'CL'
         --AND e.CLASS = 'INV'
         and d.site_use_id = e.customer_site_use_id
         and c.customer_number = p_account_number
         and d.cust_acct_site_id = f.cust_acct_site_id
         and g.party_site_id = f.party_site_id
         and e.org_id =102;