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'