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;
/