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;