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;