[Home] [Help]
3: -- Read the profile option that enables/disables the debug log
4: g_po_wf_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('PO_SET_DEBUG_WORKFLOW_ON'),'N');
5: -- Read the profile option that determines whether the promise date will be defaulted with need-by date or not
6: g_default_promise_date VARCHAR2(1) := NVL(FND_PROFILE.VALUE('POS_DEFAULT_PROMISE_DATE_ACK'),'N');
7: g_document_subtype PO_HEADERS_ALL.TYPE_LOOKUP_CODE%TYPE;
8: --Bug#3497033
9: --g_currency_format_mask declared to pass in as the second parameter
10: --in FND_CURRENCY.GET_FORMAT_MASK
11: g_currency_format_mask NUMBER := 60;
561: -- not insert into the action history.
562: --DocumentID
563: -- This value for this parameter depends on the DocumentType:
564: -- 'REQUISITION': PO_REQUISITION_HEADERS_ALL.requisition_header_id
565: -- 'PO' or 'PA': PO_HEADERS_ALL.po_header_id
566: -- 'RELEASE': PO_RELEASES_ALL.po_release_id
567: --DocumentNumber
568: -- (Obsolete) This parameter is ignored. This procedure will derive the
569: -- document number from DocumentID and DocumentType. (Bug 3284628)
574: -- 'REQUISITION', 'PO', 'PA', 'RELEASE'
575: --DocumentSubType
576: -- The value for this parameter depends on the DocumentType:
577: -- 'REQUISITION': PO_REQUISITION_HEADERS_ALL.type_lookup_code
578: -- 'PO' or 'PA': PO_HEADERS_ALL.type_lookup_code
579: -- 'RELEASE': PO_RELEASES_ALL.release_type
580: --SubmitterAction
581: -- (Unused) This parameter is not currently used.
582: --ForwardToID
634: -- value as : 'SUPPLIER' or 'REQUESTER'. Other callers will pass as NULL
635: -- value (default). The corresponding value('REQUESTER'/'SUPPLIER') is used
636: -- to set INITIATOR wf attribute in RCO wf.
637: --p_xml_flag
638: -- If 'Y' or 'N', this procedure will update the xml_flag in PO_HEADERS_ALL
639: -- or PO_RELEASES_ALL accordingly. This is used by HTML Orders. (Bug 5218538)
640: -- If null, no updates will be made.
641: -- p_source_type_code VARCHAR2 DEFAULT null
642: -- For the internal change order for requisitions the value will be INVENTORY
699: l_userkey VARCHAR2(40);
700: l_doc_num_rel VARCHAR2(100);
701: l_doc_display_name FND_NEW_MESSAGES.message_text%TYPE; -- Bug 3215186
702: l_release_num PO_RELEASES.release_num%TYPE; -- Bug 3215186
703: l_revision_num PO_HEADERS.revision_num%TYPE; -- Bug 3215186
704: l_ga_flag VARCHAR2(1) := NULL; -- FPI GA
705: /* RETROACTIVE FPI START */
706: l_seq_for_item_key VARCHAR2(25) := NULL; --Bug14305923
707: l_can_change_forward_from_flag po_document_types.can_change_forward_from_flag%type;
718: l_itemkey VARCHAR2(60);
719: l_type_name po_document_types.type_name%type;
720: /* RETROACTIVE FPI END */
721: l_drop_ship_flag po_line_locations.drop_ship_flag%type; --
722: l_conterms_exist_flag PO_HEADERS_ALL.CONTERMS_EXIST_FLAG%TYPE; --
724: l_operating_unit hr_all_organization_units_tl.name%TYPE; --
725: l_document_number PO_HEADERS_ALL.segment1%TYPE; -- Bug 3284628
726: l_consigned_flag PO_HEADERS_ALL.CONSIGNED_CONSUMPTION_FLAG%TYPE;
721: l_drop_ship_flag po_line_locations.drop_ship_flag%type; --
722: l_conterms_exist_flag PO_HEADERS_ALL.CONTERMS_EXIST_FLAG%TYPE; --
723: --bug##3682458 replaced legal entity name with operating unit
724: l_operating_unit hr_all_organization_units_tl.name%TYPE; --
725: l_document_number PO_HEADERS_ALL.segment1%TYPE; -- Bug 3284628
726: l_consigned_flag PO_HEADERS_ALL.CONSIGNED_CONSUMPTION_FLAG%TYPE;
727: l_autoapprove_retro VARCHAR2(1);
728: l_okc_doc_type VARCHAR2(20); --
729: l_vendor po_vendors.vendor_name%type; --Bug 4254468
722: l_conterms_exist_flag PO_HEADERS_ALL.CONTERMS_EXIST_FLAG%TYPE; --
723: --bug##3682458 replaced legal entity name with operating unit
724: l_operating_unit hr_all_organization_units_tl.name%TYPE; --
725: l_document_number PO_HEADERS_ALL.segment1%TYPE; -- Bug 3284628
726: l_consigned_flag PO_HEADERS_ALL.CONSIGNED_CONSUMPTION_FLAG%TYPE;
727: l_autoapprove_retro VARCHAR2(1);
728: l_okc_doc_type VARCHAR2(20); --
729: l_vendor po_vendors.vendor_name%type; --Bug 4254468
730: l_vendor_site_code po_vendor_sites_all.vendor_site_code%type; --Bug 4254468
738: l_communicatePriceChange VARCHAR2(1); -- bug4176111
739: --CLM PR Amendment
740: l_federal_flag VARCHAR2(1) := 'N';
741: l_conformed_header_id NUMBER;
742: l_clm_document_number PO_HEADERS_ALL.clm_document_number%TYPE;
743: l_modification_number PO_DRAFTS.modification_number%TYPE;
744: /* Mod Project */
745: l_is_mod VARCHAR2(1) := 'N';
746: l_ame_approval_id NUMBER;
748: l_ame_transaction_type PO_DOC_STYLE_HEADERS.ame_transaction_type%TYPE;
749: /* The new workflow with AME will be used for PO and PA */
750: /* Mod Project */
751: -- CLM CO signature ER
752: l_clm_contract_officer po_headers_all.clm_contract_officer%TYPE;
753: l_ko_sign_required VARCHAR2(1);
754: x_ko_username VARCHAR2(100);
755: x_ko_display_name VARCHAR2(240);
756: -- CLM CO signature ER
840: SET xml_flag = 'Y',
841: edi_processed_flag = 'N'
842: WHERE po_release_id = DocumentID;
843: ELSE
844: UPDATE po_headers_all
845: SET xml_flag = 'Y',
846: edi_processed_flag = 'N'
847: WHERE po_header_id = DocumentID;
848: END IF;
849: ELSIF (p_xml_flag = 'N') THEN
850: IF (DocumentTypeCode = 'RELEASE') THEN
851: UPDATE po_releases_all SET xml_flag = 'N' WHERE po_release_id = DocumentID;
852: ELSE
853: UPDATE po_headers_all SET xml_flag = 'N' WHERE po_header_id = DocumentID;
854: END IF;
855: END IF; -- p_xml_flag = 'Y'
856: END IF; -- p_xml_flag IS NOT NULL
857: -- Bug 5218538 END
885: ame_approval_id),
886: ame_transaction_type
887: INTO l_ame_approval_id,
888: l_ame_transaction_type
889: FROM po_headers_all
890: WHERE po_header_id = DocumentID;
891:
892: if l_ame_transaction_type is not null AND ( l_ame_approval_id IS NULL or l_ame_approval_id = 0) THEN
893: --if l_ame_approval_id = 0 then
891:
892: if l_ame_transaction_type is not null AND ( l_ame_approval_id IS NULL or l_ame_approval_id = 0) THEN
893: --if l_ame_approval_id = 0 then
894: select po_ame_approvals_s.nextval into l_ame_approval_id from dual;
895: update po_headers_all
896: set ame_approval_id = l_ame_approval_id,
897: ame_transaction_type = l_ame_transaction_type
898: where po_header_id = DocumentID;
899: end if;
905: phda.ame_approval_id),
906: phda.ame_transaction_type
907: INTO l_ame_approval_id,
908: l_ame_transaction_type
909: FROM po_headers_draft_all phda,
910: po_drafts pd
911: WHERE phda.po_header_id = DocumentID
912: AND phda.draft_id = DraftID
913: AND phda.draft_id = pd.draft_id;
913: AND phda.draft_id = pd.draft_id;
914:
915: if l_ame_approval_id = 0 then
916: select po_ame_approvals_s.nextval into l_ame_approval_id from dual;
917: update po_headers_draft_all
918: set ame_approval_id = l_ame_approval_id
919: where po_header_id = DocumentID
920: and draft_id = DraftID;
921: end if;
947:
948: SELECT pov.vendor_name, pvs.vendor_site_code
949: INTO l_vendor, l_vendor_site_code
950: FROM po_vendors pov,
951: po_headers poh,
952: po_vendor_sites_all pvs
953: WHERE pov.vendor_id = poh.vendor_id
954: AND poh.po_header_id = DocumentId
955: AND poh.vendor_site_id = pvs.vendor_site_id;
970: IF l_multi_mod_req_type = 'VENDOR_CHANGE' THEN
971:
972: SELECT pv.vendor_name, pvs.vendor_site_code
973: INTO l_new_vendor, l_new_vendor_site_code
974: FROM po_headers_draft_all phda,
975: po_vendors pv,
976: po_vendor_sites_all pvs
977: WHERE draft_id = DraftID
978: AND phda.vendor_id = pv.vendor_id
1095: IF DocumentTypeCode IN ('PO', 'PA') THEN
1096: BEGIN
1097: SELECT clm_document_number
1098: INTO l_clm_document_number
1099: FROM po_headers_all
1100: WHERE po_header_id = DocumentID;
1101: EXCEPTION
1102: WHEN no_data_found THEN
1103: l_clm_document_number := NULL;
1134: poh.agent_id
1135: INTO x_acceptance_required_flag,
1136: x_acceptance_due_date,
1137: x_agent_id
1138: FROM po_headers poh
1139: WHERE poh.po_header_id = DocumentID;
1140: ELSE
1141: SELECT por.acceptance_required_flag,
1142: por.acceptance_due_date,
1144: INTO x_acceptance_required_flag,
1145: x_acceptance_due_date,
1146: x_agent_id
1147: FROM po_releases por,
1148: po_headers_all poh --
1150: AND por.po_header_id = poh.po_header_id;
1151: END IF;
1152: --Mod Project
1178: -- Pass ga flag to the wf for all PA documents (BLANKET and CONTRACT)
1179: IF DocumentTypeCode = 'PA' THEN
1180: SELECT global_agreement_flag
1181: INTO l_ga_flag
1182: FROM po_headers_all
1183: WHERE po_header_id = DocumentID;
1184:
1185: PO_WF_UTIL_PKG.SetItemAttrText ( itemtype => l_itemtype, itemkey => l_itemkey, aname => 'GLOBAL_AGREEMENT_FLAG', avalue => l_ga_flag);
1186: END IF;
1199: INTO l_revision_num,
1200: l_doc_display_name,
1201: l_conterms_exist_flag, --
1202: l_document_number -- Bug 3284628
1203: FROM po_headers
1204: WHERE po_header_id = DocumentID;
1205: ELSE -- Mod Project
1206: SELECT 0,
1207: FND_MESSAGE.GET_STRING('PO','PO_MODIFICATION'),
1210: INTO l_revision_num,
1211: l_doc_display_name,
1212: l_conterms_exist_flag,
1213: l_document_number
1214: FROM po_headers_draft_all
1215: WHERE po_header_id = DocumentID
1216: AND draft_id = DraftId;
1217: END IF; -- l_is_mod = 'N' then
1218: l_doc_num_rel := l_document_number;
1238: l_release_num,
1239: l_doc_display_name,
1240: l_document_number -- Bug 3284628
1241: FROM po_releases POR,
1242: po_headers_all POH --
1244: AND POR.po_header_id = POH.po_header_id; -- JOIN
1245: l_doc_num_rel := l_document_number || '-' || l_release_num;
1246: END IF; -- DocumentTypeCode
1292: -- CLM Controls Project changes
1293: if DocumentTypeCode = 'PA' or DocumentTypeCode='PO' then
1294: SELECT org_id
1295: INTO l_org_id
1296: FROM po_headers_all
1297: WHERE po_header_id = DocumentID;
1298: l_ko_sign_required := PO_CORE_S.retrieveOptionValue(p_org_id => l_org_id,
1299: p_option_column => PO_CORE_S.g_KO_SIGNATURE_REQD_COL);
1300: --NVL(FND_PROFILE.VALUE('PO_CLM_KO_SIGNATURE_REQD'),'N');
1303: IF DraftID = -1 THEN
1304: BEGIN
1305: SELECT CLM_CONTRACT_OFFICER
1306: INTO l_clm_contract_officer
1307: FROM po_headers_all
1308: WHERE po_header_id = DocumentID;
1309:
1310: EXCEPTION
1311: WHEN no_data_found THEN
1396: WHERE po_release_id = DocumentId;
1397: ELSIF DocumentTypeCode = 'PO' THEN
1398: SELECT NVL(consigned_consumption_flag, 'N')
1399: INTO l_consigned_flag
1400: FROM po_headers_all
1401: WHERE po_header_id = DocumentId;
1402: END IF;
1403: EXCEPTION
1404: WHEN NO_DATA_FOUND THEN
1467: pvs.vendor_site_code
1468: INTO l_vendor,
1469: l_vendor_site_code
1470: FROM po_vendors pov,
1471: po_headers poh,
1472: po_vendor_sites_all pvs
1473: WHERE pov.vendor_id = poh.vendor_id
1474: AND poh.po_header_id = DocumentId
1475: AND poh.vendor_site_id = pvs.vendor_site_id;
1478: pvs.vendor_site_code
1479: INTO l_vendor,
1480: l_vendor_site_code
1481: FROM po_releases por,
1482: po_headers poh,
1483: po_vendors pov,
1484: po_vendor_sites_all pvs
1485: WHERE por.po_release_id = DocumentId
1486: AND por.po_header_id = poh.po_header_id
1615: last_update_date = sysdate
1616: WHERE po_release_id = DocumentID;
1617: ELSE --PO or PA
1618: IF l_draft_type IS NULL THEN--l_is_mod = 'N' THEN -- Mod Project
1619: UPDATE po_headers
1620: SET AUTHORIZATION_STATUS = 'IN PROCESS',
1621: last_updated_by = fnd_global.user_id,
1622: last_update_login = fnd_global.login_id,
1623: last_update_date = sysdate
1701: l_interface_source VARCHAR2(30);
1702: l_can_modify_flag VARCHAR2(1);
1703: l_view_po_url VARCHAR2(1000); -- HTML Orders R12
1704: l_edit_po_url VARCHAR2(1000); -- HTML Orders R12
1705: l_style_id po_headers_all.style_id%TYPE;
1706: l_ga_flag po_headers_all.global_agreement_flag%TYPE;
1707: /* Bug 7535468
1708: Increasing the length of x_progress from 200 to 1200 */
1709: x_progress VARCHAR2(1200);
1702: l_can_modify_flag VARCHAR2(1);
1703: l_view_po_url VARCHAR2(1000); -- HTML Orders R12
1704: l_edit_po_url VARCHAR2(1000); -- HTML Orders R12
1705: l_style_id po_headers_all.style_id%TYPE;
1706: l_ga_flag po_headers_all.global_agreement_flag%TYPE;
1707: /* Bug 7535468
1708: Increasing the length of x_progress from 200 to 1200 */
1709: x_progress VARCHAR2(1200);
1710: l_draft_id NUMBER:=-1; --Mod Project
1886: ELSIF NVL(l_ga_flag,'N') = 'Y' OR l_doc_subtype = 'STANDARD' THEN
1887: BEGIN
1888: SELECT style_id
1889: INTO l_style_id
1890: FROM po_headers_all
1891: WHERE po_header_id = l_document_id;
1892: EXCEPTION
1893: WHEN OTHERS THEN
1894: l_style_id := NULL;
2038: SELECT DECODE(draft_id, -1, AUTHORIZATION_STATUS, STATUS),
2039: NVL(REVISION_NUM,0)
2040: INTO l_authorization_status,
2041: l_po_revision
2042: FROM po_headers_merge_v
2043: WHERE PO_HEADER_ID = l_document_id
2044: AND draft_id = l_draft_id;
2045: --Mod Project end
2046: /* Bug#1810322: kagarwal
2489: WHERE draft_id = l_draft_id;
2490: ELSE
2491: SELECT NVL(authorization_status,'INCOMPLETE')
2492: INTO l_auth_stat
2493: FROM PO_HEADERS
2494: WHERE po_header_id = l_doc_id;
2495: END IF;
2496: IF (l_auth_stat <> 'APPROVED' AND l_auth_stat <> 'COMPLETED') THEN
2497: -- Adding the new parameter Draft Id
2746: WHERE requisition_header_id = l_doc_id;
2747: ELSIF l_doc_type IN ('PO', 'PA') THEN
2748: SELECT NVL(authorization_status,'INCOMPLETE')
2749: INTO l_auth_stat
2750: FROM PO_HEADERS
2751: WHERE po_header_id = l_doc_id;
2752: ELSIF l_doc_type = 'RELEASE' THEN
2753: SELECT NVL(authorization_status,'INCOMPLETE')
2754: INTO l_auth_stat
3198: l_draft_id NUMBER := -1; --Mod Project
3199: /*
3200: cursor po_cursor(p_header_id number) is
3201: select wf_item_key
3202: from po_headers
3203: where po_header_id= p_header_id;
3204: */
3205: -- CLM Aprvl Modifying the PO Cursor to use Merge Views
3206: CURSOR po_cursor(p_header_id NUMBER, p_draft_id NUMBER)
3205: -- CLM Aprvl Modifying the PO Cursor to use Merge Views
3206: CURSOR po_cursor(p_header_id NUMBER, p_draft_id NUMBER)
3207: IS
3208: SELECT wf_item_key
3209: FROM po_headers_merge_v
3210: WHERE po_header_id= p_header_id
3211: AND draft_id = p_draft_id;
3212: CURSOR req_cursor(p_header_id NUMBER)
3213: IS
3548: l_notifier_resp VARCHAR2(100);
3549: l_doc_string VARCHAR2(200);
3550: l_preparer_user_name VARCHAR2(100);
3551: -- BINDING FPJ
3552: l_acceptance_flag PO_HEADERS_ALL.acceptance_required_flag%TYPE;
3553: BEGIN
3554: x_progress := 'PO_REQAPPROVAL_INIT1.Send_WS_FYI_Notif_Yes_No: 01';
3555: IF (g_po_wf_debug = 'Y') THEN
3556: /* DEBUG */
3568: -- BINDING FPJ START
3569: IF ((l_document_type <> 'RELEASE') AND l_document_subtype IN ('STANDARD','BLANKET','CONTRACT')) THEN
3570: SELECT acceptance_required_flag
3571: INTO l_acceptance_flag
3572: FROM po_headers_all
3573: WHERE po_header_Id = l_document_id;
3574: IF l_acceptance_flag = 'S' THEN
3575: PO_REQAPPROVAL_INIT1.locate_notifier(l_document_id, l_document_type, 'Y', l_notifier, l_notifier_resp);
3576: ELSE
3958: pvs.language, pv.vendor_name
3959: INTO l_vendor_site_id,
3960: l_vendor_site_code,
3961: l_vendor_site_lang, l_vendor_name
3962: FROM po_headers poh,
3963: po_vendor_sites pvs, po_vendors pv
3964: WHERE pvs.vendor_site_id = poh.vendor_site_id
3965: AND pv.vendor_id = poh.vendor_id
3966: AND poh.po_header_id = l_po_header_id;
4242: ELSIF l_doc_type IN ('PO','PA') THEN
4243: x_progress := '003';
4244: SELECT NVL(authorization_status,'INCOMPLETE')
4245: INTO l_auth_stat
4246: FROM PO_HEADERS
4247: WHERE po_header_id = l_doc_id;
4248: ELSIF l_doc_type = 'RELEASE' THEN
4249: x_progress := '004';
4250: SELECT NVL(authorization_status,'INCOMPLETE')
4909: SAVEPOINT save_rev_num;
4910:
4911: IF l_doc_type IN ('PO', 'PA') THEN
4912:
4913: UPDATE po_headers_all
4914: SET comm_rev_num = l_po_revision_num_curr
4915: WHERE po_header_id = l_po_header_id;
4916:
4917: -- added for bug 9072034 (to update revision number for releases.)
5041: IF p_update_sign = 'Y' AND p_auth_status = 'PRE-APPROVED' THEN
5042: l_pending_signature_flag := 'Y';
5043: END IF;
5044: IF note = 'UPGRADE_TO_R11' THEN
5045: UPDATE po_headers
5046: SET WF_ITEM_TYPE = itemtype,
5047: WF_ITEM_KEY = itemkey,
5048: last_updated_by = fnd_global.user_id,
5049: last_update_login = fnd_global.login_id,
5051: WHERE po_header_id = l_po_header_id;
5052: ELSE
5053: --Mod Project
5054: IF l_draft_id = -1 THEN
5055: UPDATE po_headers
5056: SET AUTHORIZATION_STATUS = p_auth_status,
5057: WF_ITEM_TYPE = itemtype,
5058: WF_ITEM_KEY = itemkey,
5059: last_updated_by = fnd_global.user_id,
5063: ,
5064: pending_signature_flag = DECODE(l_pending_signature_flag, 'Y', DECODE(acceptance_required_flag, 'S', 'Y', 'N'), pending_signature_flag)
5065: WHERE po_header_id = l_po_header_id;
5066: ELSE
5067: UPDATE Po_Headers_Draft_all
5068: SET WF_ITEM_TYPE = itemtype,
5069: WF_ITEM_KEY = itemkey,
5070: last_updated_by = fnd_global.user_id,
5071: last_update_login = fnd_global.login_id,
5164: pragma AUTONOMOUS_TRANSACTION;
5165: x_progress VARCHAR2(3):= '000';
5166: BEGIN
5167: x_progress := '001';
5168: UPDATE po_headers
5169: SET WF_ITEM_TYPE = itemtype,
5170: WF_ITEM_KEY = itemkey,
5171: last_updated_by = fnd_global.user_id,
5172: last_update_login = fnd_global.login_id,
5251: FROM po_requisition_headers_all
5252: WHERE requisition_header_id = document_id;
5253: CURSOR get_po_orgid
5254: IS
5255: SELECT org_id FROM po_headers_all WHERE po_header_id = document_id;
5256: CURSOR get_release_orgid
5257: IS
5258: SELECT org_id FROM po_releases_all WHERE po_release_id = document_id;
5259:
5407: WHERE requisition_header_id = p_doc_id;
5408: ELSIF p_doc_type IN ('PO','PA') THEN
5409: x_progress := '003';
5410: -- Mod Project
5411: --Bug 12944203 Taking l_transaction_type from po_headers as workflow attribute is not committed at this time.
5412: IF l_draft_id = -1 THEN
5413: SELECT NVL(authorization_status,'INCOMPLETE'),
5414: revision_num,
5415: ame_transaction_type
5415: ame_transaction_type
5416: INTO l_auth_stat,
5417: l_revision_num,
5418: l_transaction_type
5419: FROM PO_HEADERS
5420: WHERE po_header_id = p_doc_id;
5421: ELSE
5422: SELECT NVL(status,'INCOMPLETE'),
5423: 0,
5426: INTO l_auth_stat,
5427: l_revision_num,
5428: l_transaction_type,
5429: l_draft_type
5430: FROM PO_HEADERS_MERGE_V
5431: WHERE po_header_id = p_doc_id
5432: AND draft_id = l_draft_id;
5433: END IF;
5434: --l_transaction_type := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype, itemkey => itemkey, aname => 'AME_TRANSACTION_TYPE');
5755: (
5756: 1
5757: )
5758: ;
5759: l_document_subtype po_headers.type_lookup_code%TYPE;
5760: /*Bug 6692126 end */
5761: l_draft_id NUMBER; -- CLM Mod
5762: BEGIN
5763: x_progress := 'PO_REQAPPROVAL_INIT1.PrintDocument: 01';
6047: (
6048: 1
6049: )
6050: ;
6051: l_document_subtype po_headers.type_lookup_code%TYPE;
6052: /*Bug 6692126 end */
6053: x_progress VARCHAR2
6054: (
6055: 200
6977: || ty.DISPLAYED_FIELD
6978: || ' '
6979: || hd.SEGMENT1
6980: INTO l_doc_string
6981: FROM po_headers hd,
6982: po_lookup_codes ty,
6983: po_lookup_codes st
6984: WHERE hd.po_header_id = l_document_id
6985: AND ty.lookup_type = 'DOCUMENT TYPE'
7009: || hd.SEGMENT1
7010: || '-'
7011: || rl.RELEASE_NUM
7012: INTO l_doc_string
7013: FROM po_headers hd,
7014: po_releases rl,
7015: po_lookup_codes ty,
7016: po_lookup_codes st
7017: WHERE rl.po_release_id = l_document_id
7308: l_doc_num VARCHAR2(30);
7309: l_sys_error_msg VARCHAR2(2000) :='';
7310: l_release_num_dash VARCHAR2(30);
7311: l_release_num NUMBER; --1942901
7312: l_document_id PO_HEADERS_ALL.po_header_id%TYPE; --
7314: ** Desc: We will get the document type display value from
7315: ** po document types.
7316: */
7405: actid IN NUMBER,
7406: funcmode IN VARCHAR2,
7407: result OUT NOCOPY VARCHAR2 )
7408: IS
7409: l_acceptance_flag po_headers_all.acceptance_required_flag%TYPE;
7410: x_progress VARCHAR2(3) := '000';
7411: l_document_id NUMBER;
7412: l_document_type po_document_types.document_type_code%type;
7413: l_document_subtype po_document_types.document_subtype%type;
7419: /*
7420: 1. Bug#2742276: To find out if acceptance is required, older version used to check workflow
7421: attribute ACCEPTANCE_REQUIRED.
7422: This may not be correct since acceptance_requried_flag may be updated in the DB.
7423: Thus, we shall query acceptance_required_flag from po_headers/po_releases view.
7424: */
7425: x_progress := '001';
7426: l_document_type := wf_engine.GetItemAttrText (itemtype => itemtype, itemkey => itemkey, aname => 'DOCUMENT_TYPE');
7427: l_document_id := wf_engine.GetItemAttrNumber (itemtype => itemtype, itemkey => itemkey, aname => 'DOCUMENT_ID');
7428: l_document_subtype := wf_engine.GetItemAttrText (itemtype => itemtype, itemkey => itemkey, aname => 'DOCUMENT_SUBTYPE');
7429: IF(l_document_type <> 'RELEASE') THEN
7430: SELECT acceptance_required_flag
7431: INTO l_acceptance_flag
7432: FROM po_headers_all --bug 4764963
7433: WHERE po_header_Id = l_document_id;
7434: ELSE
7435: SELECT acceptance_required_flag
7436: INTO l_acceptance_flag
7448: -- There should be no notification if there has been at least on reponse
7449: IF(l_document_type <> 'RELEASE') THEN
7450: SELECT revision_num
7451: INTO l_revision_num
7452: FROM po_headers_all
7453: WHERE po_header_id = l_document_id;
7454: SELECT COUNT(*)
7455: INTO l_responded_shipments
7456: FROM PO_ACCEPTANCES
7490: x_org_id NUMBER;
7491: x_user_id NUMBER;
7492: x_document_id NUMBER;
7493: x_document_type_code VARCHAR2(30);
7494: x_po_header_id po_headers_all.po_header_id%TYPE;
7495: x_vendor po_vendors.vendor_name%TYPE;
7496: /* Bug 7172641 Changing the size as equal to the column size of vendor_name in po_vendors table */
7497: x_supp_user_name VARCHAR2(100);
7498: x_supplier_displayname VARCHAR2(100);
7516: poh.revision_num
7517: INTO x_vendor,
7518: x_revision_num -- RDP
7519: FROM po_vendors pov,
7520: po_headers poh
7521: WHERE pov.vendor_id = poh.vendor_id
7522: AND poh.po_header_id=x_document_id;
7523: ELSE
7524: SELECT pov.vendor_name,
7527: INTO x_vendor,
7528: x_po_header_id,
7529: x_revision_num
7530: FROM po_releases por,
7531: po_headers_all poh, --
7533: WHERE por.po_release_id = x_document_id
7534: AND por.po_header_id = poh.po_header_id
7535: AND poh.vendor_id = pov.vendor_id;
7652: poh.revision_num
7653: INTO x_vendor,
7654: l_revision_num
7655: FROM po_vendors pov,
7656: po_headers poh
7657: WHERE pov.vendor_id = poh.vendor_id
7658: AND poh.po_header_id=x_document_id;
7659:
7660: x_progress := '002';
7664: por.revision_num
7665: INTO x_vendor,
7666: l_revision_num
7667: FROM po_releases por,
7668: po_headers_all poh, --
7670: WHERE por.po_release_id = x_document_id
7671: AND por.po_header_id = poh.po_header_id
7672: AND poh.vendor_id = pov.vendor_id;
7778: l_rowid ROWID;
7779: l_Last_Update_Login PO_ACCEPTANCES.last_update_login%TYPE;
7780: l_Last_Update_Date PO_ACCEPTANCES.last_update_date%TYPE;
7781: l_Last_Updated_By PO_ACCEPTANCES.last_updated_by%TYPE;
7782: l_acc_po_header_id PO_HEADERS_ALL.po_header_id%TYPE;
7783: l_acceptance_id PO_ACCEPTANCES.acceptance_id%TYPE;
7784: -- End of Bug 2850566
7785: l_rspndr_usr_name fnd_user.user_name%TYPE := '';
7786: l_accepting_party VARCHAR2(1);
7809: SELECT revision_num,
7810: agent_id
7811: INTO x_revision_num,
7812: x_employee_id
7813: FROM po_headers
7814: WHERE po_header_id = x_document_id;
7815: ELSE
7816: x_Po_Release_Id := x_document_id;
7817: SELECT po_header_id,
7854: p_accepted_flag => x_Accepted_Flag, p_note => x_acceptance_note, p_accepting_party => l_accepting_party );
7855: -- End of Bug 2850566 RBAIRRAJ
7856: -- Reset the Acceptance required Flag
7857: --Bug 6847039 - Start
7858: --Update the last update date when po_headers_all/po_releases_all tables are updated.
7859: IF x_po_release_id IS NOT NULL THEN
7860: UPDATE po_releases
7861: SET acceptance_required_flag = 'N',
7862: LAST_UPDATE_DATE = SYSDATE,
7862: LAST_UPDATE_DATE = SYSDATE,
7863: acceptance_due_date = ''
7864: WHERE po_release_id = x_po_release_id;
7865: ELSE
7866: UPDATE po_headers
7867: SET acceptance_required_flag = 'N',
7868: LAST_UPDATE_DATE = SYSDATE,
7869: acceptance_due_date = ''
7870: WHERE po_header_id = x_po_header_id;
8046: actid IN NUMBER,
8047: funcmode IN VARCHAR2,
8048: resultout OUT NOCOPY VARCHAR2 )
8049: IS
8050: l_document_id po_headers_all.po_header_id%type;
8051: l_vendor_id po_headers_all.vendor_id%type;
8052: l_vendor_site_id po_headers_all.vendor_site_id%type;
8053: l_progress VARCHAR2(300);
8054: l_update_releases VARCHAR2(1) := 'Y';
8047: funcmode IN VARCHAR2,
8048: resultout OUT NOCOPY VARCHAR2 )
8049: IS
8050: l_document_id po_headers_all.po_header_id%type;
8051: l_vendor_id po_headers_all.vendor_id%type;
8052: l_vendor_site_id po_headers_all.vendor_site_id%type;
8053: l_progress VARCHAR2(300);
8054: l_update_releases VARCHAR2(1) := 'Y';
8055: l_return_status VARCHAR2(1) ;
8048: resultout OUT NOCOPY VARCHAR2 )
8049: IS
8050: l_document_id po_headers_all.po_header_id%type;
8051: l_vendor_id po_headers_all.vendor_id%type;
8052: l_vendor_site_id po_headers_all.vendor_site_id%type;
8053: l_progress VARCHAR2(300);
8054: l_update_releases VARCHAR2(1) := 'Y';
8055: l_return_status VARCHAR2(1) ;
8056: l_communicate_update VARCHAR2(30); -- Bug 3574895. Length same as that on the form field PO_APPROVE.COMMUNICATE_UPDATES
8066: SELECT poh.vendor_id,
8067: poh.vendor_site_id
8068: INTO l_vendor_id,
8069: l_vendor_site_id
8070: FROM po_headers poh
8071: WHERE poh.po_header_id = l_document_id;
8072: --
8073: -- Purchasing from the view mtl_default_sets_view.
8074: BEGIN
8200: funcmode IN VARCHAR2,
8201: resultout OUT NOCOPY VARCHAR2)
8202: IS
8203: l_retro_change VARCHAR2(1);
8204: l_document_id PO_HEADERS_ALL.po_header_id%TYPE;
8205: l_document_type PO_DOCUMENT_TYPES.document_type_code%TYPE;
8206: l_progress VARCHAR2(2000);
8207: l_update_releases VARCHAR2(1) := 'Y';
8208: l_return_status VARCHAR2(1) ;
8455: x_result_out OUT NOCOPY VARCHAR2 )
8456: IS
8457: l_progress VARCHAR2(200);
8458: l_doc_id NUMBER;
8459: l_doc_type PO_HEADERS_ALL.TYPE_LOOKUP_CODE%type;
8460: l_cat_admin_user_name FND_USER.USER_NAME%type;
8461: BEGIN
8462: l_progress := '100';
8463: IF (g_po_wf_debug = 'Y') THEN
8497: -- initiator, then set the item attribute CATALOG_ADMIN_USER_NAME so that
8498: -- the catalog admin can be notified later in the workflow process.
8499: -- The reason why we are setting the attribute here instead of checking later
8500: -- in the wf process is because, the lock_owner_role/lock_owner_id will be
8501: -- cleared from po_headers_all later. So first capture the item attribute
8502: -- use it later in the workflow to decide whether a notification has to be
8503: -- sent. See Node "SHOULD_NOTIFY_CAT_ADMIN" function in the PO Approval and
8504: -- PO Approval Top Process(Also see function should_notify_cat_admin() in
8505: -- this file).
8525: p_doc_type IN VARCHAR2)
8526: IS
8527: l_progress VARCHAR2(255);
8528: l_user_name FND_USER.USER_NAME%type;
8529: l_lock_owner_role PO_HEADERS_ALL.lock_owner_role%type;
8530: l_lock_owner_user_id PO_HEADERS_ALL.lock_owner_user_id%type;
8531: BEGIN
8532: l_progress := 'PO_REQAPPROVAL_INIT1.set_catalog_admin_user_name: 100' || 'Document Id='|| TO_CHAR(p_doc_id) || 'Document Type='|| p_doc_type;
8533: IF (g_po_wf_debug = 'Y') THEN
8526: IS
8527: l_progress VARCHAR2(255);
8528: l_user_name FND_USER.USER_NAME%type;
8529: l_lock_owner_role PO_HEADERS_ALL.lock_owner_role%type;
8530: l_lock_owner_user_id PO_HEADERS_ALL.lock_owner_user_id%type;
8531: BEGIN
8532: l_progress := 'PO_REQAPPROVAL_INIT1.set_catalog_admin_user_name: 100' || 'Document Id='|| TO_CHAR(p_doc_id) || 'Document Type='|| p_doc_type;
8533: IF (g_po_wf_debug = 'Y') THEN
8534: PO_WF_DEBUG_PKG.insert_debug(p_item_type,p_item_key,l_progress);
8543: SELECT lock_owner_user_id,
8544: lock_owner_role
8545: INTO l_lock_owner_user_id,
8546: l_lock_owner_role
8547: FROM po_headers_all
8548: WHERE po_header_id = p_doc_id;
8549:
8550: l_progress := 'PO_REQAPPROVAL_INIT1.set_catalog_admin_user_name: 140' || 'l_lock_owner_user_id ='|| TO_CHAR(l_lock_owner_user_id) || 'l_lock_owner_role Type='|| l_lock_owner_role;
8551: IF (g_po_wf_debug = 'Y') THEN
8793:
8794: --
8795: -- Made the procedure non-autonomous
8796: -- There was deadlock error occurring when the communication was invoked
8797: -- from Cancel as the Cancel code also updates the po_headers_all/po_releases_all tables
8798: -- and the Commit/Rollback will not happen when the communication is invoked.
8799: PROCEDURE update_print_count( p_doc_id NUMBER,
8800: p_doc_type VARCHAR2 )
8801: IS
8806: SET pr.printed_date = sysdate,
8807: pr.print_count = NVL(pr.print_count,0) + 1
8808: WHERE pr.po_release_id = p_doc_id ;
8809: ELSIF (p_doc_type IN ('PO','PA')) THEN
8810: UPDATE po_headers_all ph
8811: SET ph.printed_date = sysdate,
8812: ph.print_count = NVL(ph.print_count,0) + 1
8813: WHERE ph.po_header_id = p_doc_id ;
8814: END IF;
8952: INTO l_itemtype,
8953: l_workflow_process,
8954: l_ame_transaction_type
8955: FROM po_doc_style_headers ds,
8956: po_headers_merge_v phm
8957: WHERE phm.po_header_id = DocumentId
8958: AND NVL(phm.draft_id, -1) = DraftId
8959: AND phm.style_id = ds.style_id
8960: AND ds.wf_approval_itemtype IS NOT NULL
9075: aname => 'DOCUMENT_TYPE');
9076:
9077: IF l_doc_type IN ('PO', 'PA') THEN
9078:
9079: UPDATE po_headers_all
9080: SET comm_rev_num = l_po_revision_num_curr
9081: WHERE po_header_id = l_po_header_id;
9082:
9083: -- added for bug 9072034 (to update revision number for releases.)