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');