1: PACKAGE BODY ASO_SHIPMENT_PVT as
2: /* $Header: asovshpb.pls 120.5 2011/09/05 06:38:46 akushwah ship $ */
3: --
4: -- NAME
5: -- ASO_SHIPMENT_PVT
1: PACKAGE BODY ASO_SHIPMENT_PVT as
2: /* $Header: asovshpb.pls 120.5 2011/09/05 06:38:46 akushwah ship $ */
3: --
4: -- NAME
5: -- ASO_SHIPMENT_PVT
6: --
7: -- HISTORY
8: --
9:
6: --
7: -- HISTORY
8: --
9:
10: G_PKG_NAME CONSTANT VARCHAR2(30):= 'ASO_SHIPMENT_PVT';
11:
12: PROCEDURE Delete_shipment(
13: P_Api_Version_Number IN NUMBER,
14: P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
298:
299: BEGIN
300:
301: IF aso_debug_pub.g_debug_flag = 'Y' THEN
302: aso_debug_pub.add('ASO_SHIPMENT_PVT: Get_cust_to_party_site_id: Begin');
303: END IF;
304:
305: OPEN c_inv_site1;
306: FETCH c_inv_site1 INTO l_inv_site_id;
570: /* End : Code change for Bug 12938390(11i Bug 10253993) */
571:
572: BEGIN
573: IF aso_debug_pub.g_debug_flag = 'Y' THEN
574: aso_debug_pub.add('ASO_SHIPMENT_PVT:p_qte_header_id ' || p_qte_header_id, 1, 'N');
575: aso_debug_pub.add('ASO_SHIPMENT_PVT:quote_line_id ' || p_qte_line_id, 1, 'N');
576: aso_debug_pub.add('ASO_SHIPMENT_PVT:shipment_id ' || p_shipment_id, 1, 'N');
577: END IF;
578:
571:
572: BEGIN
573: IF aso_debug_pub.g_debug_flag = 'Y' THEN
574: aso_debug_pub.add('ASO_SHIPMENT_PVT:p_qte_header_id ' || p_qte_header_id, 1, 'N');
575: aso_debug_pub.add('ASO_SHIPMENT_PVT:quote_line_id ' || p_qte_line_id, 1, 'N');
576: aso_debug_pub.add('ASO_SHIPMENT_PVT:shipment_id ' || p_shipment_id, 1, 'N');
577: END IF;
578:
579: OPEN c_ship_site1;
572: BEGIN
573: IF aso_debug_pub.g_debug_flag = 'Y' THEN
574: aso_debug_pub.add('ASO_SHIPMENT_PVT:p_qte_header_id ' || p_qte_header_id, 1, 'N');
575: aso_debug_pub.add('ASO_SHIPMENT_PVT:quote_line_id ' || p_qte_line_id, 1, 'N');
576: aso_debug_pub.add('ASO_SHIPMENT_PVT:shipment_id ' || p_shipment_id, 1, 'N');
577: END IF;
578:
579: OPEN c_ship_site1;
580: FETCH c_ship_site1 INTO l_ship_site_id, l_ship_cust_acct_id;
579: OPEN c_ship_site1;
580: FETCH c_ship_site1 INTO l_ship_site_id, l_ship_cust_acct_id;
581:
582: IF aso_debug_pub.g_debug_flag = 'Y' THEN
583: aso_debug_pub.add('ASO_SHIPMENT_PVT:c_ship_site1 l_ship_site_id ' || l_ship_site_id, 1, 'N');
584: aso_debug_pub.add('ASO_SHIPMENT_PVT:c_ship_site1 l_ship_cust_acct_id ' || l_ship_cust_acct_id, 1, 'N');
585: END IF;
586:
587: /* Start : Code change for Bug 12938390(11i Bug 12426838) , changed position of this cursor */
580: FETCH c_ship_site1 INTO l_ship_site_id, l_ship_cust_acct_id;
581:
582: IF aso_debug_pub.g_debug_flag = 'Y' THEN
583: aso_debug_pub.add('ASO_SHIPMENT_PVT:c_ship_site1 l_ship_site_id ' || l_ship_site_id, 1, 'N');
584: aso_debug_pub.add('ASO_SHIPMENT_PVT:c_ship_site1 l_ship_cust_acct_id ' || l_ship_cust_acct_id, 1, 'N');
585: END IF;
586:
587: /* Start : Code change for Bug 12938390(11i Bug 12426838) , changed position of this cursor */
588: Open C_SHIPMENT;
588: Open C_SHIPMENT;
589: Fetch C_SHIPMENT INTO l_shipment_id;
590: Close C_SHIPMENT;
591:
592: aso_debug_pub.add('ASO_SHIPMENT_PVT: At the start , l_shipment_id : '||l_shipment_id);
593:
594: /* End : Code change for Bug 12938390(11i Bug 12426838) */
595:
596: IF c_ship_site1%FOUND and l_ship_site_id IS NOT NULL and l_ship_site_id <> FND_API.G_MISS_NUM THEN
601: OPEN c_ship_cust_id;
602: FETCH c_ship_cust_id into l_cust_id;
603:
604: IF aso_debug_pub.g_debug_flag = 'Y' THEN
605: aso_debug_pub.add('ASO_SHIPMENT_PVT:c_ship_cust_id l_cust_id ' || l_cust_id, 1, 'N');
606: END IF;
607:
608: IF c_ship_cust_id%NOTFOUND or l_cust_id IS NULL or l_cust_id = FND_API.G_MISS_NUM THEN
609:
613: OPEN c_ship_cust_id1(l_shipment_id); -- Code change done for Bug 12938390(11i Bug 12426838)
614: FETCH c_ship_cust_id1 into l_cust_id;
615:
616: IF aso_debug_pub.g_debug_flag = 'Y' THEN
617: aso_debug_pub.add('ASO_SHIPMENT_PVT:c_ship_cust_id1 l_cust_id ' || l_cust_id, 1, 'N');
618: END IF;
619:
620: IF c_ship_cust_id1%NOTFOUND or l_cust_id IS NULL or l_cust_id = FND_API.G_MISS_NUM THEN
621:
626: FETCH c_cust_id INTO l_cust_id;
627: CLOSE c_cust_id;
628:
629: IF aso_debug_pub.g_debug_flag = 'Y' THEN
630: aso_debug_pub.add('ASO_SHIPMENT_PVT:c_cust_id l_cust_id ' || l_cust_id, 1, 'N');
631: END IF;
632:
633: OPEN C_site_use(l_cust_id , l_ship_site_id);
634: FETCH C_site_use into l_ship_site_use_id;
633: OPEN C_site_use(l_cust_id , l_ship_site_id);
634: FETCH C_site_use into l_ship_site_use_id;
635:
636: IF aso_debug_pub.g_debug_flag = 'Y' THEN
637: aso_debug_pub.add('ASO_SHIPMENT_PVT:C_site_use1 l_ship_site_use_id ' || l_ship_site_use_id, 1, 'N');
638: END IF;
639: CLOSE C_site_use;
640:
641: return l_ship_site_use_id;
647: OPEN C_site_use(l_cust_id , l_ship_site_id);
648: FETCH C_site_use into l_ship_site_use_id;
649:
650: IF aso_debug_pub.g_debug_flag = 'Y' THEN
651: aso_debug_pub.add('ASO_SHIPMENT_PVT:C_site_use2 l_ship_site_use_id ' || l_ship_site_use_id, 1, 'N');
652: END IF;
653: CLOSE C_site_use;
654:
655: return l_ship_site_use_id;
664: OPEN C_site_use(l_cust_id , l_ship_site_id);
665: FETCH C_site_use into l_ship_site_use_id;
666:
667: IF aso_debug_pub.g_debug_flag = 'Y' THEN
668: aso_debug_pub.add('ASO_SHIPMENT_PVT:C_site_use3 l_ship_site_use_id ' || l_ship_site_use_id, 1, 'N');
669: END IF;
670: CLOSE C_site_use;
671:
672: return l_ship_site_use_id;
733: Open C_SHIPMENT;
734: Fetch C_SHIPMENT INTO l_shipment_id;
735: Close C_SHIPMENT;
736:
737: aso_debug_pub.add('ASO_SHIPMENT_PVT:c_ship_site2 l_shipment_id : '||l_shipment_id); */
738:
739: OPEN c_ship_site2(l_shipment_id);
740: /* End : Code change for Bug 12938390(11i Bug 10253993) */
741:
742: --OPEN c_ship_site2;
743: FETCH c_ship_site2 INTO l_ship_site_id, l_ship_cust_acct_id;
744:
745: IF aso_debug_pub.g_debug_flag = 'Y' THEN
746: aso_debug_pub.add('ASO_SHIPMENT_PVT:c_ship_site2 l_ship_site_id ' || l_ship_site_id, 1, 'N');
747: aso_debug_pub.add('ASO_SHIPMENT_PVT:c_ship_site2 l_ship_cust_acct_id ' || l_ship_cust_acct_id, 1, 'N');
748: END IF;
749:
750: IF c_ship_site2%FOUND AND l_ship_site_id IS NOT NULL and l_ship_site_id <> FND_API.G_MISS_NUM THEN
743: FETCH c_ship_site2 INTO l_ship_site_id, l_ship_cust_acct_id;
744:
745: IF aso_debug_pub.g_debug_flag = 'Y' THEN
746: aso_debug_pub.add('ASO_SHIPMENT_PVT:c_ship_site2 l_ship_site_id ' || l_ship_site_id, 1, 'N');
747: aso_debug_pub.add('ASO_SHIPMENT_PVT:c_ship_site2 l_ship_cust_acct_id ' || l_ship_cust_acct_id, 1, 'N');
748: END IF;
749:
750: IF c_ship_site2%FOUND AND l_ship_site_id IS NOT NULL and l_ship_site_id <> FND_API.G_MISS_NUM THEN
751:
754: OPEN c_ship_cust_id;
755: FETCH c_ship_cust_id into l_cust_id;
756:
757: IF aso_debug_pub.g_debug_flag = 'Y' THEN
758: aso_debug_pub.add('ASO_SHIPMENT_PVT:c_ship_cust_id l_cust_id ' || l_cust_id, 1, 'N');
759: END IF;
760:
761: IF c_ship_cust_id%NOTFOUND or l_cust_id IS NULL or l_cust_id = FND_API.G_MISS_NUM THEN
762:
767:
768: FETCH c_ship_cust_id1 into l_cust_id;
769:
770: IF aso_debug_pub.g_debug_flag = 'Y' THEN
771: aso_debug_pub.add('ASO_SHIPMENT_PVT:c_ship_cust_id1 l_cust_id ' || l_cust_id, 1, 'N');
772: END IF;
773:
774: IF c_ship_cust_id1%NOTFOUND or l_cust_id IS NULL or l_cust_id = FND_API.G_MISS_NUM THEN
775:
779: FETCH c_cust_id INTO l_cust_id;
780: CLOSE c_cust_id;
781:
782: IF aso_debug_pub.g_debug_flag = 'Y' THEN
783: aso_debug_pub.add('ASO_SHIPMENT_PVT:c_cust_id l_cust_id ' || l_cust_id, 1, 'N');
784: END IF;
785:
786: OPEN C_site_use(l_cust_id , l_ship_site_id);
787: FETCH C_site_use into l_ship_site_use_id;
786: OPEN C_site_use(l_cust_id , l_ship_site_id);
787: FETCH C_site_use into l_ship_site_use_id;
788:
789: IF aso_debug_pub.g_debug_flag = 'Y' THEN
790: aso_debug_pub.add('ASO_SHIPMENT_PVT:C_site_use4 l_ship_site_use_id ' || l_ship_site_use_id, 1, 'N');
791: END IF;
792:
793: CLOSE C_site_use;
794: return l_ship_site_use_id;
800: OPEN C_site_use(l_cust_id , l_ship_site_id);
801: FETCH C_site_use into l_ship_site_use_id;
802:
803: IF aso_debug_pub.g_debug_flag = 'Y' THEN
804: aso_debug_pub.add('ASO_SHIPMENT_PVT:C_site_use5 l_ship_site_use_id ' || l_ship_site_use_id, 1, 'N');
805: END IF;
806:
807: CLOSE C_site_use;
808: return l_ship_site_use_id;
815: OPEN C_site_use(l_cust_id , l_ship_site_id);
816: FETCH C_site_use into l_ship_site_use_id;
817:
818: IF aso_debug_pub.g_debug_flag = 'Y' THEN
819: aso_debug_pub.add('ASO_SHIPMENT_PVT:C_site_use6 l_ship_site_use_id ' || l_ship_site_use_id, 1, 'N');
820: END IF;
821: CLOSE C_site_use;
822:
823: return l_ship_site_use_id;
1148:
1149:
1150: Begin
1151: IF aso_debug_pub.g_debug_flag = 'Y' THEN
1152: aso_debug_pub.add('ASO_SHIPMENT_PVT.Get_ship_from_org_id: p_qte_header_id :'||p_qte_header_id, 1, 'N');
1153: aso_debug_pub.add('ASO_SHIPMENT_PVT.Get_ship_from_org_id: p_qte_line_id :'||p_qte_line_id, 1, 'N');
1154: END IF;
1155:
1156: -- Look for line-shipment level ship_from_org_id
1149:
1150: Begin
1151: IF aso_debug_pub.g_debug_flag = 'Y' THEN
1152: aso_debug_pub.add('ASO_SHIPMENT_PVT.Get_ship_from_org_id: p_qte_header_id :'||p_qte_header_id, 1, 'N');
1153: aso_debug_pub.add('ASO_SHIPMENT_PVT.Get_ship_from_org_id: p_qte_line_id :'||p_qte_line_id, 1, 'N');
1154: END IF;
1155:
1156: -- Look for line-shipment level ship_from_org_id
1157:
1158: open c_line_shipment;
1159: fetch c_line_shipment into l_ship_from_org_id;
1160:
1161: IF aso_debug_pub.g_debug_flag = 'Y' THEN
1162: aso_debug_pub.add('ASO_SHIPMENT_PVT.Get_ship_from_org_id: c_line_shipment: l_ship_from_org_id'||l_ship_from_org_id, 1, 'N');
1163: END IF;
1164:
1165: -- Line-shipment level ship_from_org_id doesn't exist then look for header-shipment level ship_from_org_id
1166:
1168: open c_header_shipment;
1169: fetch c_header_shipment into l_ship_from_org_id;
1170:
1171: IF aso_debug_pub.g_debug_flag = 'Y' THEN
1172: aso_debug_pub.add('ASO_SHIPMENT_PVT.Get_ship_from_org_id: c_header_shipment: l_ship_from_org_id'||l_ship_from_org_id, 1, 'N');
1173: END IF;
1174:
1175: close c_header_shipment;
1176: END IF;
1199:
1200: Begin
1201:
1202: IF aso_debug_pub.g_debug_flag = 'Y' THEN
1203: aso_debug_pub.add('ASO_SHIPMENT_PVT.Get_ship_method_code: p_qte_header_id : '||p_qte_header_id, 1, 'Y');
1204: aso_debug_pub.add('ASO_SHIPMENT_PVT.Get_ship_method_code: p_qte_line_id : '||p_qte_line_id, 1, 'Y');
1205: END IF;
1206:
1207: -- Look for line-shipment level ship_method_code
1200: Begin
1201:
1202: IF aso_debug_pub.g_debug_flag = 'Y' THEN
1203: aso_debug_pub.add('ASO_SHIPMENT_PVT.Get_ship_method_code: p_qte_header_id : '||p_qte_header_id, 1, 'Y');
1204: aso_debug_pub.add('ASO_SHIPMENT_PVT.Get_ship_method_code: p_qte_line_id : '||p_qte_line_id, 1, 'Y');
1205: END IF;
1206:
1207: -- Look for line-shipment level ship_method_code
1208:
1269:
1270: Begin
1271:
1272: IF aso_debug_pub.g_debug_flag = 'Y' THEN
1273: aso_debug_pub.add('ASO_SHIPMENT_PVT.Get_demand_class_code: p_qte_header_id : '||p_qte_header_id, 1, 'Y');
1274: aso_debug_pub.add('ASO_SHIPMENT_PVT.Get_demand_class_code: p_qte_line_id : '||p_qte_line_id, 1, 'Y');
1275: END IF;
1276:
1277: -- Look for line-shipment level demand_class_code
1270: Begin
1271:
1272: IF aso_debug_pub.g_debug_flag = 'Y' THEN
1273: aso_debug_pub.add('ASO_SHIPMENT_PVT.Get_demand_class_code: p_qte_header_id : '||p_qte_header_id, 1, 'Y');
1274: aso_debug_pub.add('ASO_SHIPMENT_PVT.Get_demand_class_code: p_qte_line_id : '||p_qte_line_id, 1, 'Y');
1275: END IF;
1276:
1277: -- Look for line-shipment level demand_class_code
1278:
1339:
1340: Begin
1341:
1342: IF aso_debug_pub.g_debug_flag = 'Y' THEN
1343: aso_debug_pub.add('ASO_SHIPMENT_PVT.Get_ship_to_party_site_id: p_qte_header_id : '||p_qte_header_id, 1, 'Y');
1344: aso_debug_pub.add('ASO_SHIPMENT_PVT.Get_ship_to_party_site_id: p_qte_line_id : '||p_qte_line_id, 1, 'Y');
1345: END IF;
1346:
1347: -- Look for line-shipment level ship_to_party_site_id
1340: Begin
1341:
1342: IF aso_debug_pub.g_debug_flag = 'Y' THEN
1343: aso_debug_pub.add('ASO_SHIPMENT_PVT.Get_ship_to_party_site_id: p_qte_header_id : '||p_qte_header_id, 1, 'Y');
1344: aso_debug_pub.add('ASO_SHIPMENT_PVT.Get_ship_to_party_site_id: p_qte_line_id : '||p_qte_line_id, 1, 'Y');
1345: END IF;
1346:
1347: -- Look for line-shipment level ship_to_party_site_id
1348:
1409:
1410: Begin
1411:
1412: IF aso_debug_pub.g_debug_flag = 'Y' THEN
1413: aso_debug_pub.add('ASO_SHIPMENT_PVT.Get_ship_to_cust_account_id: p_qte_header_id : '||p_qte_header_id, 1, 'Y');
1414: aso_debug_pub.add('ASO_SHIPMENT_PVT.Get_ship_to_cust_account_id: p_qte_line_id : '||p_qte_line_id, 1, 'Y');
1415: END IF;
1416:
1417: -- Look for line-shipment level ship_to_cust_account_id
1410: Begin
1411:
1412: IF aso_debug_pub.g_debug_flag = 'Y' THEN
1413: aso_debug_pub.add('ASO_SHIPMENT_PVT.Get_ship_to_cust_account_id: p_qte_header_id : '||p_qte_header_id, 1, 'Y');
1414: aso_debug_pub.add('ASO_SHIPMENT_PVT.Get_ship_to_cust_account_id: p_qte_line_id : '||p_qte_line_id, 1, 'Y');
1415: END IF;
1416:
1417: -- Look for line-shipment level ship_to_cust_account_id
1418:
1457: return l_ship_to_cust_account_id;
1458:
1459: End Get_ship_to_cust_account_id;
1460:
1461: END ASO_SHIPMENT_PVT;