Oracle Query to get Oracle Menus & Functions along with Sub Menu's and Their functions

SELECT LPAD(' ', 6*(LEVEL-1))

  || menu_entry.entry_sequence sequence ,

  LPAD(' ', 6*(LEVEL-1))

  || menu.user_menu_name SubMenu_Description ,

  LPAD(' ', 6*(LEVEL-1))

  || func.user_function_name Function_Description ,

  menu.menu_id ,

  func.function_id ,

  menu_entry.grant_flag Grant_Flag ,

  DECODE( menu_entry.sub_menu_id , NULL, 'FUNCTION' , DECODE( menu_entry.function_id , NULL, 'SUBMENU' , 'BOTH') ) Type

FROM fnd_menu_entries menu_entry ,

  fnd_menus_tl menu ,

  fnd_form_functions_tl func

WHERE menu_entry.sub_menu_id    = menu.menu_id(+)

AND menu_entry.function_id      = func.function_id(+)

AND grant_flag                  = 'Y'

  START WITH menu_entry.menu_id =

  (SELECT menu_id

  FROM fnd_menus_tl menu2

  WHERE menu2.user_menu_name = 'XXX_OM_USER'

  )

  CONNECT BY MENU_ENTRY.MENU_ID = PRIOR MENU_ENTRY.SUB_MENU_ID

ORDER SIBLINGS BY menu_entry.entry_sequence;

Deleting a Sales order Line and Adding a attachment at header level stating that Line deleted from order and with other Reason of deleting and comments if any

DECLARE
    P_LINE_REC VARCHAR2(300) :='12345,56784,454562';
    P_REASON   VARCHAR2(300) := 'Customer No Longer Needed';
    p_comments VARCHAR2(300) := 'New Code developed to add the Attachement';
     TYPE SolCurTyp
IS
  ref
  CURSOR;
    ONT_CV SOLCURTYP;
     ONT_CV1 SOLCURTYP;
      ONT_STMT VARCHAR2(4000);
       ONT_STMT1 VARCHAR2(4000);
     L_LINE_ID OE_ORDER_LINES_ALL.LINE_ID%TYPE;
     L_ORG_ID OE_ORDER_LINES_ALL.ORG_ID%type;
    L_HEADER_ID OE_ORDER_LINES_ALL.HEADER_ID%type;
    L_RETURN_STATUS varchar2(1);
    L_RET_STATUS VARCHAR2(1);   
    l_msg_data      VARCHAR2(2000);
    l_Head_id oe_order_lines_all.Header_id%Type := NULL;
    P_ATTACH_LINE_REC VARCHAR2(2000) := NULL;   
    l_msg_count              NUMBER;
    L_MESSAGE_DATA           varchar (2000);   
    l_ret_message            VARCHAR (2000);  
    L_MSG_INDEX_OUT          NUMBER;
    V_ONT_STMT    VARCHAR2(4000);
    V_COUNT NUMBER;
      V_COUNT1 NUMBER;
      v_header_id number;
 
  BEGIN
 
   ONT_STMT1 := 'SELECT distinct Header_id
                  FROM oe_order_lines_all
                  WHERE line_id in (' ||p_line_rec|| ')
                  group by Header_id';
      
 OPEN ONT_CV1 FOR ONT_STMT1;
    LOOP
      FETCH ONT_CV1 INTO V_HEADER_ID;
      EXIT  WHEN ONT_CV1%NOTFOUND;
 DBMS_OUTPUT.PUT_LINE( 'V_COUNT1 ---'||V_HEADER_ID);
     
    ONT_STMT := 'SELECT Header_id,line_id,Org_id
                  FROM oe_order_lines_all
                  WHERE line_id in (' ||p_line_rec|| ')
                  AND HEADER_ID = ( '|| V_HEADER_ID ||')
                  ORDER by Header_id';
        
    OPEN ONT_CV FOR ONT_STMT;
    LOOP
      FETCH ONT_CV INTO l_Header_id,l_line_id,l_Org_id;
      EXIT WHEN ONT_CV%NOTFOUND;
    IF l_line_id IS NOT NULL THEN
      -- Calling the API to delete a line from existing order
       DBMS_OUTPUT.PUT_LINE( 'V_COUNT2 ---'||l_line_id);
         
      DBMS_OUTPUT.PUT_LINE( 'Before Calling API for line id'||L_LINE_ID ||'---'||l_Org_id);
      OE_ORDER_PUB.DELETE_LINE ( P_LINE_ID => L_LINE_ID ,
                                 P_ORG_ID => L_ORG_ID ,
                                 P_OPERATING_UNIT => NULL ,
                                 X_RETURN_STATUS => L_RETURN_STATUS ,
                                 X_MSG_COUNT => L_MSG_COUNT ,
                                 X_MSG_DATA => L_MSG_DATA);
                                  DBMS_OUTPUT.PUT_LINE( 'After Calling API -' ||l_return_status);
         IF l_return_status = fnd_api.g_ret_sts_success
            THEN
               COMMIT;
               l_ret_status := l_return_status;
               L_RET_MESSAGE := L_MSG_DATA;
                IF   P_ATTACH_LINE_REC IS NOT NULL THEN
                      P_ATTACH_LINE_REC := P_ATTACH_LINE_REC||CHR(10) ||L_LINE_ID;
                ELSE
                       P_ATTACH_LINE_REC := L_LINE_ID;
                END IF;
            ELSE              
               oe_msg_pub.get (p_msg_index          => 1,
                               p_encoded            => fnd_api.g_false,
                               p_data               => l_msg_data,
                               p_msg_index_out      => l_msg_index_out
                              );
               l_ret_status := l_return_status;
               L_RET_MESSAGE := L_MSG_DATA;             
  DBMS_OUTPUT.PUT_LINE( 'After Calling API -'|| 'Error at SO Line Deletion: '||'Return Status of Hearder ID and Line ID -  '||L_LINE_ID||' & '||L_HEADER_ID || ' is :'||
                          L_RET_STATUS || 'and Message is :' ||L_RET_MESSAGE );
 
FND_FILE.PUT_LINE (FND_FILE.log,
                         'Error at SO Line Deletion: '||'Return Status of Hearder ID and Line ID -  '||L_LINE_ID||' & '||L_HEADER_ID || ' is :'||
                          L_RET_STATUS || 'and Message is :' ||L_RET_MESSAGE);                       
          END IF;
   END IF;
   DBMS_OUTPUT.PUT_LINE('Coming here 1');

 
  END LOOP;
  CLOSE ONT_CV;
 
  IF P_ATTACH_LINE_REC IS NOT NULL THEN
 
 DBMS_OUTPUT.PUT_LINE( 'Adding attachment for header id :'||L_HEADER_ID);
       XX_ADD_HEADER_ATTACHEMENT(p_pk1_value  => l_Header_id,
                       p_org_id     => NULL,
                       P_SHORT_TEXT => P_REASON||CHR(10) ||P_COMMENTS||CHR(10)||P_ATTACH_LINE_REC);
                       P_ATTACH_LINE_REC := NULL;
                       DBMS_OUTPUT.PUT_LINE( 'completed adding attachment for header id :'||l_Header_id);
 END IF;
 
  END LOOP;
  CLOSE ONT_CV1;
     
  EXCEPTION WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE( 'API Eror :'||SQLERRM);
END ;

Adding Attachment @ Header Level

DECLARE
p_pk1_value fnd_attached_documents.pk1_value%TYPE := '259330';
P_ORG_ID NUMBER := 204;
p_short_text varchar2(4000) := 'Successfully Deleted the Line' ;
     
    l_rowid ROWID;
    l_attached_document_id NUMBER;
    l_document_id NUMBER;
    l_media_id NUMBER;
    l_category_id NUMBER;
    l_pk1_value fnd_attached_documents.pk1_value%TYPE;
    l_seq_num NUMBER;
    l_fnd_user_id NUMBER := fnd_profile.VALUE('USER_ID');
    l_short_datatype_id NUMBER;
    l_entity_name VARCHAR2(100) := 'OE_ORDER_HEADERS';
    l_category_name VARCHAR2(100) := 'Short Text';--'Sales Order Lines Deletion';
   -- p_org_id number := fnd_profile.value(‘org_id’);
 BEGIN
    l_pk1_value := p_pk1_value;
 dbms_output.put_line( 'l_pk1_value :-'||l_pk1_value);
    SELECT fnd_documents_s.NEXTVAL
    INTO l_document_id
    FROM DUAL;
DBMS_OUTPUT.PUT_LINE( 'l_document_id :-'||l_document_id);
    SELECT fnd_attached_documents_s.NEXTVAL
    INTO l_attached_document_id
    FROM DUAL;
DBMS_OUTPUT.PUT_LINE( 'l_attached_document_id :-'||l_attached_document_id);
    SELECT NVL (MAX (seq_num), 0) + 10
    INTO l_seq_num
    FROM fnd_attached_documents
    WHERE pk1_value = l_pk1_value
    AND entity_name = l_entity_name;
DBMS_OUTPUT.PUT_LINE( 'l_seq_num :-'||l_seq_num);
    -- Get Data type id for Short Text types of attachments
    SELECT datatype_id
    INTO l_short_datatype_id
    FROM apps.fnd_document_datatypes
    WHERE NAME = 'SHORT_TEXT';
    -- Select Category id for Attachments
    DBMS_OUTPUT.PUT_LINE( 'l_short_datatype_id :-'||l_short_datatype_id);
   
    SELECT category_id
    INTO l_category_id
    FROM apps.fnd_document_categories_vl
    WHERE USER_NAME = l_category_name;
    -- This package allows user to share file across multiple orgs or restrict to single org
 DBMS_OUTPUT.PUT_LINE( 'l_category_id :-'||l_category_id);
    fnd_documents_pkg.insert_row
    (x_rowid => l_rowid,
     x_document_id => l_document_id,
     x_creation_date => SYSDATE,
     x_created_by => l_fnd_user_id,-- fnd_profile.value('USER_ID')
     x_last_update_date => SYSDATE,
     x_last_updated_by => l_fnd_user_id,-- fnd_profile.value('USER_ID')
     x_last_update_login => fnd_profile.VALUE('LOGIN_ID'),
     x_datatype_id => l_short_datatype_id, -- SHORT TEXT
     x_security_id => Null,--p_org_id,
     x_publish_flag => 'Y', --This flag allow the file to share across multiple organization
     x_category_id => l_category_id,
     x_security_type => 4,--1 tBC
     x_usage_type => 'O',
     x_language => 'US',
     x_media_id => l_media_id
    );
DBMS_OUTPUT.PUT_LINE( 'l_media_id :-'||l_media_id);
    -- Insert a new record into the table containing the sort text
    INSERT INTO FND_DOCUMENTS_SHORT_TEXT
    (media_id, short_text
    )
    VALUES (l_media_id, p_short_text
            );
DBMS_OUTPUT.PUT_LINE( 'p_short_text :-'||p_short_text);
    fnd_documents_pkg.insert_tl_row
   (x_document_id => l_document_id,
    x_creation_date => SYSDATE,
    x_created_by => l_fnd_user_id,--fnd_profile.VALUE('USER_ID'),
    x_last_update_date => SYSDATE,
    x_last_updated_by => l_fnd_user_id,--fnd_profile.VALUE('USER_ID'),
    x_last_update_login => fnd_profile.VALUE('LOGIN_ID'),
    x_language => 'US'
    );
DBMS_OUTPUT.PUT_LINE( 'Success :- 1');
   fnd_attached_documents_pkg.insert_row
   ( x_rowid => l_rowid,
     x_attached_document_id => l_attached_document_id,
     x_document_id => l_document_id,
     x_creation_date => SYSDATE,
     x_created_by => l_fnd_user_id,--fnd_profile.VALUE('USER_ID'),
     x_last_update_date => SYSDATE,
     x_last_updated_by => l_fnd_user_id,--fnd_profile.VALUE('USER_ID'),
     x_last_update_login => fnd_profile.VALUE('LOGIN_ID'),
     x_seq_num => l_seq_num,
     x_entity_name => l_entity_name,
     x_column1 => NULL,
     x_pk1_value => l_pk1_value,
     x_pk2_value => NULL,
     x_pk3_value => NULL,
     x_pk4_value => NULL,
     x_pk5_value => NULL,
     x_automatically_added_flag => 'N',
     x_datatype_id => l_short_datatype_id,
     x_category_id => l_category_id,
     x_security_type => 4,
     x_security_id => p_org_id,
     x_publish_flag => 'Y',
     x_language => 'US',
     x_media_id => l_media_id
    );
DBMS_OUTPUT.PUT_LINE( 'Success :- 2');
 commit;
Exception
  WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE( 'Error from Above :'||SQLERRM);
END;

Sales order Line Delete API

SET SERVEROUTPUT ON;
DECLARE
v_api_version_number           NUMBER  := 1;
v_return_status                VARCHAR2 (2000);
v_msg_count                    NUMBER;
v_msg_data                     VARCHAR2 (2000);
-- IN Variables --
V_HEADER_REC                   OE_ORDER_PUB.HEADER_REC_TYPE;
 r_line_rec                    oe_order_pub.line_rec_type;
v_line_tbl                     oe_order_pub.line_tbl_type;
v_action_request_tbl           oe_order_pub.request_tbl_type;
v_line_adj_tbl                 oe_order_pub.line_adj_tbl_type;
-- OUT Variables --
v_header_rec_out               oe_order_pub.header_rec_type;
v_header_val_rec_out           oe_order_pub.header_val_rec_type;
v_header_adj_tbl_out           oe_order_pub.header_adj_tbl_type;
v_header_adj_val_tbl_out       oe_order_pub.header_adj_val_tbl_type;
v_header_price_att_tbl_out     oe_order_pub.header_price_att_tbl_type;
v_header_adj_att_tbl_out       oe_order_pub.header_adj_att_tbl_type;
v_header_adj_assoc_tbl_out     oe_order_pub.header_adj_assoc_tbl_type;
v_header_scredit_tbl_out       oe_order_pub.header_scredit_tbl_type;
v_header_scredit_val_tbl_out   oe_order_pub.header_scredit_val_tbl_type;
v_line_tbl_out                 oe_order_pub.line_tbl_type;
v_line_val_tbl_out             oe_order_pub.line_val_tbl_type;
v_line_adj_tbl_out             oe_order_pub.line_adj_tbl_type;
v_line_adj_val_tbl_out         oe_order_pub.line_adj_val_tbl_type;
v_line_price_att_tbl_out       oe_order_pub.line_price_att_tbl_type;
v_line_adj_att_tbl_out         oe_order_pub.line_adj_att_tbl_type;
v_line_adj_assoc_tbl_out       oe_order_pub.line_adj_assoc_tbl_type;
v_line_scredit_tbl_out         oe_order_pub.line_scredit_tbl_type;
v_line_scredit_val_tbl_out     oe_order_pub.line_scredit_val_tbl_type;
v_lot_serial_tbl_out           oe_order_pub.lot_serial_tbl_type;
v_lot_serial_val_tbl_out       oe_order_pub.lot_serial_val_tbl_type;
v_action_request_tbl_out       oe_order_pub.request_tbl_type;
BEGIN
DBMS_OUTPUT.PUT_LINE('Starting of script');
v_line_tbl           := oe_order_pub.g_miss_line_tbl;
r_line_rec           := oe_order_pub.g_miss_line_rec;
R_LINE_REC.LINE_ID   := 449280;
r_line_rec.operation := OE_GLOBALS.G_OPR_DELETE;
v_line_tbl (1)       := r_line_rec;
DBMS_OUTPUT.PUT_LINE('Starting of API');
-- CALLING THE API TO DELETE AN ORDER LINE--
OE_ORDER_PUB.PROCESS_ORDER (
p_api_version_number            => v_api_version_number
, p_header_rec                  => v_header_rec
, p_line_tbl                    => v_line_tbl
, p_action_request_tbl          => v_action_request_tbl
, p_line_adj_tbl                => v_line_adj_tbl
-- OUT variables
, x_header_rec                  => v_header_rec_out
, x_header_val_rec              => v_header_val_rec_out
, x_header_adj_tbl              => v_header_adj_tbl_out
, x_header_adj_val_tbl          => v_header_adj_val_tbl_out
, x_header_price_att_tbl        => v_header_price_att_tbl_out
, x_header_adj_att_tbl          => v_header_adj_att_tbl_out
, x_header_adj_assoc_tbl        => v_header_adj_assoc_tbl_out
, x_header_scredit_tbl          => v_header_scredit_tbl_out
, x_header_scredit_val_tbl      => v_header_scredit_val_tbl_out
, x_line_tbl                    => v_line_tbl_out
, x_line_val_tbl                => v_line_val_tbl_out
, x_line_adj_tbl                => v_line_adj_tbl_out
, x_line_adj_val_tbl            => v_line_adj_val_tbl_out
, x_line_price_att_tbl          => v_line_price_att_tbl_out
, x_line_adj_att_tbl            => v_line_adj_att_tbl_out
, x_line_adj_assoc_tbl          => v_line_adj_assoc_tbl_out
, x_line_scredit_tbl            => v_line_scredit_tbl_out
, x_line_scredit_val_tbl        => v_line_scredit_val_tbl_out
, x_lot_serial_tbl              => v_lot_serial_tbl_out
, x_lot_serial_val_tbl          => v_lot_serial_val_tbl_out
, x_action_request_tbl          => v_action_request_tbl_out
, x_return_status               => v_return_status
, x_msg_count                   => v_msg_count
, x_msg_data                    => v_msg_data
);
DBMS_OUTPUT.PUT_LINE('Completion of API');
IF v_return_status = fnd_api.g_ret_sts_success THEN
  COMMIT;
  DBMS_OUTPUT.put_line ('Order Deletion Success : '||v_header_rec_out.header_id);
ELSE
  DBMS_OUTPUT.put_line ('Order Deletion failed:'||v_msg_data);
  ROLLBACK;
  FOR i IN 1 .. v_msg_count
  LOOP
    v_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => 'F');
    dbms_output.put_line( i|| ') '|| v_msg_data);
  END LOOP;
END IF;
END;
/

Oracle Workflow some imp scripts..

Oracle Workflow some imp scripts..

BEGIN --> purging the records from WF_ITEMS and WF_ITEM_ACTIVITY_STATUSES
WF_ENGINE.AbortProcess(ITEMTYPE,ITEMKEY,NULL,'FORCE');
WF_PURGE.Items(ITEMTYPE=>'XXXXX',ITEMKEY=>'XXXXX',ENDDATE=>SYSDATE,DOCOMMIT=>TRUE,FORCE=>TRUE);
END;

BEGIN              -- creates a record entry in WF_ITEMS TABLE
wf_engine.createprocess(itemtype => 'ITEMTYPE',itemkey => 'ITEMKEY',process => 'PROCESS_NAME');
COMMIT;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(fnd_file.output,'In exception while call to wf_engine.createprocess : '||SQLERRM);
END;
       
BEGIN -- creates the action history in WF_ITEM_ACTIVITY_STATUSES table
wf_engine.startprocess(itemtype     => 'ITEMTYPE',itemkey      => 'ITEMKEY');
COMMIT;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(fnd_file.output,'In exception while call to wf_engine.createprocess : '||SQLERRM);
END;

Regards,
Sal.

Oracle Quoting form Ship to information Query

Oracle Quoting form Ship to information Query...

SELECT hcsua.location address1, hl.address1 address2, hl.address2 address3,
  hl.address3 address4, hl.address4 city, hl.city state, hl.state postal_code,
   hl.postal_code county, hl.county province, hcsua.site_use_id country_code,
  hpsu.primary_per_type primary_per_type, terr.territory_short_name country,
  hcsua.site_use_code site_use_type, hp.party_name party_name,
  hps.party_site_id
FROM
 HZ_PARTIES hp,
 HZ_PARTY_SITES hps,
 HZ_LOCATIONS hl,
 FND_TERRITORIES_VL terr,
 HZ_CUST_SITE_USES_ALL hcsua,
 HZ_CUST_ACCT_SITES_ALL hcasa,
 HZ_PARTY_SITE_USES hpsu
WHERE hp.party_id = ?
AND hcasa.org_id = ?
AND hcsua.site_use_code = 'SHIP_TO'
AND hps.party_id = hp.party_id
AND hps.location_id = hl.location_id
AND hcsua.CUST_ACCT_SITE_ID = hcasa.CUST_ACCT_SITE_ID
AND hcasa.party_site_id = hps.party_site_id
AND hl.country = terr.territory_code
AND hpsu.party_site_id = hps.party_site_id
AND hcsua.site_use_code = hpsu.site_use_type
AND hp.status = 'A'
AND hps.status = 'A'
AND hpsu.status='A'
ORDER BY hp.party_name, hl.address1, hl.address2, hl.address3, hl.address4

Regards,
Sal.

Oracle Quoting Customer information Query

Oracle Quoting Customer information Query..

SELECT hp.party_name, hp.party_number, ca.account_number, hp.party_type,
  hp.party_id, ca.cust_account_id
FROM
 HZ_PARTIES HP,
 HZ_CUST_ACCOUNTS CA
WHERE ( UPPER(PARTY_NAME) LIKE :1
 AND (PARTY_NAME LIKE :2))
 AND ( hp.party_type in ('PERSON','ORGANIZATION')
 AND hp.status = 'A'
 AND trunc(nvl(ca.account_activation_date,sysdate)) <= trunc(sysdate)
 AND trunc(nvl(ca.account_termination_date,sysdate)) >= trunc(sysdate)
 AND HP.party_id = CA.party_id
 AND ca.status = 'A'
 and rownum <= 5000 ) ORDER BY party_name,account_number

Regards,
Sal.