60: END IF;
61:
62:
63: -- Debug Message
64: JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: ' || l_api_name || 'start');
65:
66:
67: -- Initialize API return status to SUCCESS
68: x_return_status := FND_API.G_RET_STS_SUCCESS;
118:
119: IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
120: THEN
121: -- Debug message
122: JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: Validate_pack_serial_lots');
123:
124: -- Invoke validation procedures
125: Validate_pack_serial_lots(
126: p_init_msg_list => FND_API.G_FALSE,
124: -- Invoke validation procedures
125: Validate_pack_serial_lots(
126: p_init_msg_list => FND_API.G_FALSE,
127: p_validation_level => p_validation_level,
128: p_validation_mode => JTF_PLSQL_API.G_CREATE,
129: P_plsl_Rec => P_plsl_Rec,
130: x_return_status => x_return_status,
131: x_msg_count => x_msg_count,
132: x_msg_data => x_msg_data);
138:
139: -- Hint: Add corresponding Master-Detail business logic here if necessary.
140:
141: -- Debug Message
142: JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: Calling create table handler');
143:
144: IF l_created_by IS NULL THEN
145: l_created_by := G_USER_ID;
146: END IF;
189: END IF;
190:
191:
192: -- Debug Message
193: JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: ' || l_api_name || 'end');
194:
195:
196: -- Standard call to get message count and if count is 1, get message info.
197: FND_MSG_PUB.Count_And_Get
217: END IF;
218: */
219: EXCEPTION
220: WHEN FND_API.G_EXC_ERROR THEN
221: JTF_PLSQL_API.HANDLE_EXCEPTIONS(
222: P_API_NAME => L_API_NAME
223: ,P_PKG_NAME => G_PKG_NAME
224: ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
225: ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
221: JTF_PLSQL_API.HANDLE_EXCEPTIONS(
222: P_API_NAME => L_API_NAME
223: ,P_PKG_NAME => G_PKG_NAME
224: ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
225: ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
226: ,X_MSG_COUNT => X_MSG_COUNT
227: ,X_MSG_DATA => X_MSG_DATA
228: ,X_RETURN_STATUS => X_RETURN_STATUS);
229:
227: ,X_MSG_DATA => X_MSG_DATA
228: ,X_RETURN_STATUS => X_RETURN_STATUS);
229:
230: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
231: JTF_PLSQL_API.HANDLE_EXCEPTIONS(
232: P_API_NAME => L_API_NAME
233: ,P_PKG_NAME => G_PKG_NAME
234: ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
235: ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
231: JTF_PLSQL_API.HANDLE_EXCEPTIONS(
232: P_API_NAME => L_API_NAME
233: ,P_PKG_NAME => G_PKG_NAME
234: ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
235: ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
236: ,X_MSG_COUNT => X_MSG_COUNT
237: ,X_MSG_DATA => X_MSG_DATA
238: ,X_RETURN_STATUS => X_RETURN_STATUS);
239:
237: ,X_MSG_DATA => X_MSG_DATA
238: ,X_RETURN_STATUS => X_RETURN_STATUS);
239:
240: WHEN OTHERS THEN
241: JTF_PLSQL_API.HANDLE_EXCEPTIONS(
242: P_API_NAME => L_API_NAME
243: ,P_PKG_NAME => G_PKG_NAME
244: ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
245: ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
240: WHEN OTHERS THEN
241: JTF_PLSQL_API.HANDLE_EXCEPTIONS(
242: P_API_NAME => L_API_NAME
243: ,P_PKG_NAME => G_PKG_NAME
244: ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
245: ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
246: ,X_MSG_COUNT => X_MSG_COUNT
247: ,X_MSG_DATA => X_MSG_DATA
248: ,X_RETURN_STATUS => X_RETURN_STATUS);
241: JTF_PLSQL_API.HANDLE_EXCEPTIONS(
242: P_API_NAME => L_API_NAME
243: ,P_PKG_NAME => G_PKG_NAME
244: ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
245: ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
246: ,X_MSG_COUNT => X_MSG_COUNT
247: ,X_MSG_DATA => X_MSG_DATA
248: ,X_RETURN_STATUS => X_RETURN_STATUS);
249: End Create_pack_serial_lots;
310: END IF;
311:
312:
313: -- Debug Message
314: JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: ' || l_api_name || 'start');
315:
316:
317: -- Initialize API return status to SUCCESS
318: x_return_status := FND_API.G_RET_STS_SUCCESS;
353: RAISE FND_API.G_EXC_ERROR;
354: END IF;
355: */
356: -- Debug Message
357: JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: - Open Cursor to Select');
358:
359: /*
360: Open C_Get_pack_serial_lots( l_tar_plsl_rec.PACKLIST_SERIAL_LOT_ID);
361:
383: END IF;
384: raise FND_API.G_EXC_ERROR;
385: END IF;
386: -- Debug Message
387: JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: - Close Cursor');
388: Close C_Get_pack_serial_lots;
389: */
390:
391:
412:
413: IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
414: THEN
415: -- Debug message
416: JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: Validate_pack_serial_lots');
417:
418: -- Invoke validation procedures
419: Validate_pack_serial_lots(
420: p_init_msg_list => FND_API.G_FALSE,
418: -- Invoke validation procedures
419: Validate_pack_serial_lots(
420: p_init_msg_list => FND_API.G_FALSE,
421: p_validation_level => p_validation_level,
422: p_validation_mode => JTF_PLSQL_API.G_UPDATE,
423: P_plsl_Rec => P_plsl_Rec,
424: x_return_status => x_return_status,
425: x_msg_count => x_msg_count,
426: x_msg_data => x_msg_data);
432:
433: -- Hint: Add corresponding Master-Detail business logic here if necessary.
434:
435: -- Debug Message
436: JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: Calling update table handler');
437:
438: -- Invoke table handler(CSP_PACKLIST_SERIAL_LOTS_PKG.Update_Row)
439: CSP_PACKLIST_SERIAL_LOTS_PKG.Update_Row(
440: p_PACKLIST_SERIAL_LOT_ID => p_plsl_rec.PACKLIST_SERIAL_LOT_ID,
460: END IF;
461:
462:
463: -- Debug Message
464: JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: ' || l_api_name || 'end');
465:
466:
467: -- Standard call to get message count and if count is 1, get message info.
468: FND_MSG_PUB.Count_And_Get
489: END IF;
490: */
491: EXCEPTION
492: WHEN FND_API.G_EXC_ERROR THEN
493: JTF_PLSQL_API.HANDLE_EXCEPTIONS(
494: P_API_NAME => L_API_NAME
495: ,P_PKG_NAME => G_PKG_NAME
496: ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
497: ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
493: JTF_PLSQL_API.HANDLE_EXCEPTIONS(
494: P_API_NAME => L_API_NAME
495: ,P_PKG_NAME => G_PKG_NAME
496: ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
497: ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
498: ,X_MSG_COUNT => X_MSG_COUNT
499: ,X_MSG_DATA => X_MSG_DATA
500: ,X_RETURN_STATUS => X_RETURN_STATUS);
501:
499: ,X_MSG_DATA => X_MSG_DATA
500: ,X_RETURN_STATUS => X_RETURN_STATUS);
501:
502: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
503: JTF_PLSQL_API.HANDLE_EXCEPTIONS(
504: P_API_NAME => L_API_NAME
505: ,P_PKG_NAME => G_PKG_NAME
506: ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
507: ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
503: JTF_PLSQL_API.HANDLE_EXCEPTIONS(
504: P_API_NAME => L_API_NAME
505: ,P_PKG_NAME => G_PKG_NAME
506: ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
507: ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
508: ,X_MSG_COUNT => X_MSG_COUNT
509: ,X_MSG_DATA => X_MSG_DATA
510: ,X_RETURN_STATUS => X_RETURN_STATUS);
511:
509: ,X_MSG_DATA => X_MSG_DATA
510: ,X_RETURN_STATUS => X_RETURN_STATUS);
511:
512: WHEN OTHERS THEN
513: JTF_PLSQL_API.HANDLE_EXCEPTIONS(
514: P_API_NAME => L_API_NAME
515: ,P_PKG_NAME => G_PKG_NAME
516: ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
517: ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
512: WHEN OTHERS THEN
513: JTF_PLSQL_API.HANDLE_EXCEPTIONS(
514: P_API_NAME => L_API_NAME
515: ,P_PKG_NAME => G_PKG_NAME
516: ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
517: ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
518: ,X_MSG_COUNT => X_MSG_COUNT
519: ,X_MSG_DATA => X_MSG_DATA
520: ,X_RETURN_STATUS => X_RETURN_STATUS);
513: JTF_PLSQL_API.HANDLE_EXCEPTIONS(
514: P_API_NAME => L_API_NAME
515: ,P_PKG_NAME => G_PKG_NAME
516: ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
517: ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
518: ,X_MSG_COUNT => X_MSG_COUNT
519: ,X_MSG_DATA => X_MSG_DATA
520: ,X_RETURN_STATUS => X_RETURN_STATUS);
521: End Update_pack_serial_lots;
560: END IF;
561:
562:
563: -- Debug Message
564: JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: ' || l_api_name || 'start');
565:
566:
567: -- Initialize API return status to SUCCESS
568: x_return_status := FND_API.G_RET_STS_SUCCESS;
603: RAISE FND_API.G_EXC_ERROR;
604: END IF;
605: */
606: -- Debug Message
607: JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: Calling delete table handler');
608:
609: -- Invoke table handler(CSP_PACKLIST_SERIAL_LOTS_PKG.Delete_Row)
610: CSP_PACKLIST_SERIAL_LOTS_PKG.Delete_Row(
611: p_PACKLIST_SERIAL_LOT_ID => p_plsl_rec.PACKLIST_SERIAL_LOT_ID);
620: END IF;
621:
622:
623: -- Debug Message
624: JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: ' || l_api_name || 'end');
625:
626:
627: -- Standard call to get message count and if count is 1, get message info.
628: FND_MSG_PUB.Count_And_Get
649: END IF;
650: */
651: EXCEPTION
652: WHEN FND_API.G_EXC_ERROR THEN
653: JTF_PLSQL_API.HANDLE_EXCEPTIONS(
654: P_API_NAME => L_API_NAME
655: ,P_PKG_NAME => G_PKG_NAME
656: ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
657: ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
653: JTF_PLSQL_API.HANDLE_EXCEPTIONS(
654: P_API_NAME => L_API_NAME
655: ,P_PKG_NAME => G_PKG_NAME
656: ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
657: ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
658: ,X_MSG_COUNT => X_MSG_COUNT
659: ,X_MSG_DATA => X_MSG_DATA
660: ,X_RETURN_STATUS => X_RETURN_STATUS);
661:
659: ,X_MSG_DATA => X_MSG_DATA
660: ,X_RETURN_STATUS => X_RETURN_STATUS);
661:
662: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
663: JTF_PLSQL_API.HANDLE_EXCEPTIONS(
664: P_API_NAME => L_API_NAME
665: ,P_PKG_NAME => G_PKG_NAME
666: ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
667: ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
663: JTF_PLSQL_API.HANDLE_EXCEPTIONS(
664: P_API_NAME => L_API_NAME
665: ,P_PKG_NAME => G_PKG_NAME
666: ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
667: ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
668: ,X_MSG_COUNT => X_MSG_COUNT
669: ,X_MSG_DATA => X_MSG_DATA
670: ,X_RETURN_STATUS => X_RETURN_STATUS);
671:
669: ,X_MSG_DATA => X_MSG_DATA
670: ,X_RETURN_STATUS => X_RETURN_STATUS);
671:
672: WHEN OTHERS THEN
673: JTF_PLSQL_API.HANDLE_EXCEPTIONS(
674: P_API_NAME => L_API_NAME
675: ,P_PKG_NAME => G_PKG_NAME
676: ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
677: ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
672: WHEN OTHERS THEN
673: JTF_PLSQL_API.HANDLE_EXCEPTIONS(
674: P_API_NAME => L_API_NAME
675: ,P_PKG_NAME => G_PKG_NAME
676: ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
677: ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
678: ,X_MSG_COUNT => X_MSG_COUNT
679: ,X_MSG_DATA => X_MSG_DATA
680: ,X_RETURN_STATUS => X_RETURN_STATUS);
673: JTF_PLSQL_API.HANDLE_EXCEPTIONS(
674: P_API_NAME => L_API_NAME
675: ,P_PKG_NAME => G_PKG_NAME
676: ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
677: ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
678: ,X_MSG_COUNT => X_MSG_COUNT
679: ,X_MSG_DATA => X_MSG_DATA
680: ,X_RETURN_STATUS => X_RETURN_STATUS);
681: End Delete_pack_serial_lots;
688: )
689: IS
690: BEGIN
691: -- Debug Message
692: JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: Define Columns Begins');
693:
694: -- define all columns for CSP_PACKLIST_SERIAL_LOTS_V view
695: dbms_sql.define_column(p_cur_get_plsl, 1, P_plsl_Rec.PACKLIST_SERIAL_LOT_ID);
696: dbms_sql.define_column(p_cur_get_plsl, 2, P_plsl_Rec.PACKLIST_LINE_ID);
700: dbms_sql.define_column(p_cur_get_plsl, 6, P_plsl_Rec.LOT_NUMBER, 240);
701: dbms_sql.define_column(p_cur_get_plsl, 7, P_plsl_Rec.SERIAL_NUMBER, 240);
702:
703: -- Debug Message
704: JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: Define Columns Ends');
705: END Define_Columns;
706:
707: -- This procudure gets column values by the Dynamic SQL.
708: PROCEDURE Get_Column_Values(
711: )
712: IS
713: BEGIN
714: -- Debug Message
715: JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: Get Column Values Begins');
716:
717: -- get all column values for CSP_PACKLIST_SERIAL_LOTS_V table
718: dbms_sql.column_value(p_cur_get_plsl, 1, X_plsl_Rec.ROW_ID);
719: dbms_sql.column_value(p_cur_get_plsl, 2, X_plsl_Rec.PACKLIST_SERIAL_LOT_ID);
724: dbms_sql.column_value(p_cur_get_plsl, 7, X_plsl_Rec.LOT_NUMBER);
725: dbms_sql.column_value(p_cur_get_plsl, 8, X_plsl_Rec.SERIAL_NUMBER);
726:
727: -- Debug Message
728: JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: Get Column Values Ends');
729: END Get_Column_Values;
730:
731: PROCEDURE Gen_plsl_order_cl(
732: p_order_by_rec IN CSP_Pack_Serial_Lots_PUB.plsl_sort_rec_type,
736: x_msg_data OUT NOCOPY VARCHAR2
737: )
738: IS
739: l_order_by_cl VARCHAR2(1000) := NULL;
740: l_util_order_by_tbl JTF_PLSQL_API.Util_order_by_tbl_type;
741: BEGIN
742: -- Debug Message
743: JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: Generate Order by Begins');
744:
739: l_order_by_cl VARCHAR2(1000) := NULL;
740: l_util_order_by_tbl JTF_PLSQL_API.Util_order_by_tbl_type;
741: BEGIN
742: -- Debug Message
743: JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: Generate Order by Begins');
744:
745: -- Hint: Developer should add more statements according to CSP_sort_rec_type
746: -- Ex:
747: -- l_util_order_by_tbl(1).col_choice := p_order_by_rec.customer_name;
747: -- l_util_order_by_tbl(1).col_choice := p_order_by_rec.customer_name;
748: -- l_util_order_by_tbl(1).col_name := 'Customer_Name';
749:
750: -- Debug Message
751: JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Invoke JTF_PLSQL_API.Translate_OrderBy');
752:
753: JTF_PLSQL_API.Translate_OrderBy(
754: p_api_version_number => 1.0
755: ,p_init_msg_list => FND_API.G_FALSE
749:
750: -- Debug Message
751: JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Invoke JTF_PLSQL_API.Translate_OrderBy');
752:
753: JTF_PLSQL_API.Translate_OrderBy(
754: p_api_version_number => 1.0
755: ,p_init_msg_list => FND_API.G_FALSE
756: ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
757: ,p_order_by_tbl => l_util_order_by_tbl
766: x_order_by_cl := NULL;
767: END IF;
768:
769: -- Debug Message
770: JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: Generate Order by Ends');
771: END Gen_plsl_order_cl;
772:
773: -- This procedure bind the variables for the Dynamic SQL
774: PROCEDURE Bind(
780: BEGIN
781: -- Bind variables
782: -- Only those that are not NULL
783: -- Debug Message
784: JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: Bind Variables Begins');
785:
786: -- The following example applies to all columns,
787: -- developers can copy and paste them.
788: IF( (P_plsl_Rec.PACKLIST_SERIAL_LOT_ID IS NOT NULL) AND (P_plsl_Rec.PACKLIST_SERIAL_LOT_ID <> FND_API.G_MISS_NUM) )
790: DBMS_SQL.BIND_VARIABLE(p_cur_get_plsl, ':p_PACKLIST_SERIAL_LOT_ID', P_plsl_Rec.PACKLIST_SERIAL_LOT_ID);
791: END IF;
792:
793: -- Debug Message
794: JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: Bind Variables Ends');
795: END Bind;
796:
797: PROCEDURE Gen_Select(
798: x_select_cl OUT NOCOPY VARCHAR2
799: )
800: IS
801: BEGIN
802: -- Debug Message
803: JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: Generate Select Begins');
804:
805: x_select_cl := 'Select ' ||
806: 'CSP_PACKLIST_SERIAL_LOTS_V.ROW_ID,' ||
807: 'CSP_PACKLIST_SERIAL_LOTS_V.PACKLIST_SERIAL_LOT_ID,' ||
817: 'CSP_PACKLIST_SERIAL_LOTS_V.LOT_NUMBER,' ||
818: 'CSP_PACKLIST_SERIAL_LOTS_V.SERIAL_NUMBER,' ||
819: 'from CSP_PACKLIST_SERIAL_LOTS_V';
820: -- Debug Message
821: JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: Generate Select Ends');
822:
823: END Gen_Select;
824:
825: PROCEDURE Gen_plsl_Where(
841: str_csr2 NUMBER;
842: l_operator VARCHAR2(10);
843: BEGIN
844: -- Debug Message
845: JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: Generate Where Begins');
846:
847: -- There are three examples for each kind of datatype:
848: -- NUMBER, DATE, VARCHAR2.
849: -- Developer can copy and paste the following codes for your own record.
896:
897: -- Add more IF statements for each column below
898:
899: -- Debug Message
900: JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: Generate Where Ends');
901:
902: END Gen_plsl_Where;
903:
904: -- Item-level validation procedures
905: PROCEDURE Validate_PL_SERIAL_LOT_ID (
906: P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
907: P_Validation_mode IN VARCHAR2,
908: P_PACKLIST_SERIAL_LOT_ID IN NUMBER,
909: -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY JTF_PLSQL_API.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
910: X_Return_Status OUT NOCOPY VARCHAR2,
911: X_Msg_Count OUT NOCOPY NUMBER,
912: X_Msg_Data OUT NOCOPY VARCHAR2
913: )
926:
927: -- validate NOT NULL column
928: IF(p_PACKLIST_SERIAL_LOT_ID is NULL)
929: THEN
930: JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR, 'CSP', 'Private pack_serial_lots API: -Violate NOT NULL constraint(PACKLIST_SERIAL_LOT_ID)');
931: x_return_status := FND_API.G_RET_STS_ERROR;
932: END IF;
933:
934: IF(p_validation_mode = JTF_PLSQL_API.G_CREATE)
930: JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR, 'CSP', 'Private pack_serial_lots API: -Violate NOT NULL constraint(PACKLIST_SERIAL_LOT_ID)');
931: x_return_status := FND_API.G_RET_STS_ERROR;
932: END IF;
933:
934: IF(p_validation_mode = JTF_PLSQL_API.G_CREATE)
935: THEN
936: -- Hint: Validate data
937: -- IF p_PACKLIST_SERIAL_LOT_ID is not NULL and p_PACKLIST_SERIAL_LOT_ID <> G_MISS_CHAR
938: -- verify if data is valid
937: -- IF p_PACKLIST_SERIAL_LOT_ID is not NULL and p_PACKLIST_SERIAL_LOT_ID <> G_MISS_CHAR
938: -- verify if data is valid
939: -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
940: NULL;
941: ELSIF(p_validation_mode = JTF_PLSQL_API.G_UPDATE)
942: THEN
943: -- Hint: Validate data
944: -- IF p_PACKLIST_SERIAL_LOT_ID <> G_MISS_CHAR
945: -- verify if data is valid
959: PROCEDURE Validate_PACKLIST_LINE_ID (
960: P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
961: P_Validation_mode IN VARCHAR2,
962: P_PACKLIST_LINE_ID IN NUMBER,
963: -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY JTF_PLSQL_API.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
964: X_Return_Status OUT NOCOPY VARCHAR2,
965: X_Msg_Count OUT NOCOPY NUMBER,
966: X_Msg_Data OUT NOCOPY VARCHAR2
967: )
980:
981: -- validate NOT NULL column
982: IF(p_PACKLIST_LINE_ID is NULL)
983: THEN
984: JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR, 'CSP', 'Private pack_serial_lots API: -Violate NOT NULL constraint(PACKLIST_LINE_ID)');
985: x_return_status := FND_API.G_RET_STS_ERROR;
986: END IF;
987:
988: IF(p_validation_mode = JTF_PLSQL_API.G_CREATE)
984: JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR, 'CSP', 'Private pack_serial_lots API: -Violate NOT NULL constraint(PACKLIST_LINE_ID)');
985: x_return_status := FND_API.G_RET_STS_ERROR;
986: END IF;
987:
988: IF(p_validation_mode = JTF_PLSQL_API.G_CREATE)
989: THEN
990: -- Hint: Validate data
991: -- IF p_PACKLIST_LINE_ID is not NULL and p_PACKLIST_LINE_ID <> G_MISS_CHAR
992: -- verify if data is valid
991: -- IF p_PACKLIST_LINE_ID is not NULL and p_PACKLIST_LINE_ID <> G_MISS_CHAR
992: -- verify if data is valid
993: -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
994: NULL;
995: ELSIF(p_validation_mode = JTF_PLSQL_API.G_UPDATE)
996: THEN
997: -- Hint: Validate data
998: -- IF p_PACKLIST_LINE_ID <> G_MISS_CHAR
999: -- verify if data is valid
1013: PROCEDURE Validate_ORGANIZATION_ID (
1014: P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1015: P_Validation_mode IN VARCHAR2,
1016: P_ORGANIZATION_ID IN NUMBER,
1017: -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY JTF_PLSQL_API.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
1018: X_Return_Status OUT NOCOPY VARCHAR2,
1019: X_Msg_Count OUT NOCOPY NUMBER,
1020: X_Msg_Data OUT NOCOPY VARCHAR2
1021: )
1034:
1035: -- validate NOT NULL column
1036: IF(p_ORGANIZATION_ID is NULL)
1037: THEN
1038: JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR, 'CSP', 'Private pack_serial_lots API: -Violate NOT NULL constraint(ORGANIZATION_ID)');
1039: x_return_status := FND_API.G_RET_STS_ERROR;
1040: END IF;
1041:
1042: IF(p_validation_mode = JTF_PLSQL_API.G_CREATE)
1038: JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR, 'CSP', 'Private pack_serial_lots API: -Violate NOT NULL constraint(ORGANIZATION_ID)');
1039: x_return_status := FND_API.G_RET_STS_ERROR;
1040: END IF;
1041:
1042: IF(p_validation_mode = JTF_PLSQL_API.G_CREATE)
1043: THEN
1044: -- Hint: Validate data
1045: -- IF p_ORGANIZATION_ID is not NULL and p_ORGANIZATION_ID <> G_MISS_CHAR
1046: -- verify if data is valid
1045: -- IF p_ORGANIZATION_ID is not NULL and p_ORGANIZATION_ID <> G_MISS_CHAR
1046: -- verify if data is valid
1047: -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1048: NULL;
1049: ELSIF(p_validation_mode = JTF_PLSQL_API.G_UPDATE)
1050: THEN
1051: -- Hint: Validate data
1052: -- IF p_ORGANIZATION_ID <> G_MISS_CHAR
1053: -- verify if data is valid
1067: PROCEDURE Validate_INVENTORY_ITEM_ID (
1068: P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1069: P_Validation_mode IN VARCHAR2,
1070: P_INVENTORY_ITEM_ID IN NUMBER,
1071: -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY JTF_PLSQL_API.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
1072: X_Return_Status OUT NOCOPY VARCHAR2,
1073: X_Msg_Count OUT NOCOPY NUMBER,
1074: X_Msg_Data OUT NOCOPY VARCHAR2
1075: )
1088:
1089: -- validate NOT NULL column
1090: IF(p_INVENTORY_ITEM_ID is NULL)
1091: THEN
1092: JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR, 'CSP', 'Private pack_serial_lots API: -Violate NOT NULL constraint(INVENTORY_ITEM_ID)');
1093: x_return_status := FND_API.G_RET_STS_ERROR;
1094: END IF;
1095:
1096: IF(p_validation_mode = JTF_PLSQL_API.G_CREATE)
1092: JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR, 'CSP', 'Private pack_serial_lots API: -Violate NOT NULL constraint(INVENTORY_ITEM_ID)');
1093: x_return_status := FND_API.G_RET_STS_ERROR;
1094: END IF;
1095:
1096: IF(p_validation_mode = JTF_PLSQL_API.G_CREATE)
1097: THEN
1098: -- Hint: Validate data
1099: -- IF p_INVENTORY_ITEM_ID is not NULL and p_INVENTORY_ITEM_ID <> G_MISS_CHAR
1100: -- verify if data is valid
1099: -- IF p_INVENTORY_ITEM_ID is not NULL and p_INVENTORY_ITEM_ID <> G_MISS_CHAR
1100: -- verify if data is valid
1101: -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1102: NULL;
1103: ELSIF(p_validation_mode = JTF_PLSQL_API.G_UPDATE)
1104: THEN
1105: -- Hint: Validate data
1106: -- IF p_INVENTORY_ITEM_ID <> G_MISS_CHAR
1107: -- verify if data is valid
1121: PROCEDURE Validate_QUANTITY (
1122: P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1123: P_Validation_mode IN VARCHAR2,
1124: P_QUANTITY IN NUMBER,
1125: -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY JTF_PLSQL_API.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
1126: X_Return_Status OUT NOCOPY VARCHAR2,
1127: X_Msg_Count OUT NOCOPY NUMBER,
1128: X_Msg_Data OUT NOCOPY VARCHAR2
1129: )
1142:
1143: -- validate NOT NULL column
1144: IF(p_QUANTITY is NULL)
1145: THEN
1146: JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR, 'CSP', 'Private pack_serial_lots API: -Violate NOT NULL constraint(QUANTITY)');
1147: x_return_status := FND_API.G_RET_STS_ERROR;
1148: END IF;
1149:
1150: IF(p_validation_mode = JTF_PLSQL_API.G_CREATE)
1146: JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR, 'CSP', 'Private pack_serial_lots API: -Violate NOT NULL constraint(QUANTITY)');
1147: x_return_status := FND_API.G_RET_STS_ERROR;
1148: END IF;
1149:
1150: IF(p_validation_mode = JTF_PLSQL_API.G_CREATE)
1151: THEN
1152: -- Hint: Validate data
1153: -- IF p_QUANTITY is not NULL and p_QUANTITY <> G_MISS_CHAR
1154: -- verify if data is valid
1153: -- IF p_QUANTITY is not NULL and p_QUANTITY <> G_MISS_CHAR
1154: -- verify if data is valid
1155: -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1156: NULL;
1157: ELSIF(p_validation_mode = JTF_PLSQL_API.G_UPDATE)
1158: THEN
1159: -- Hint: Validate data
1160: -- IF p_QUANTITY <> G_MISS_CHAR
1161: -- verify if data is valid
1175: PROCEDURE Validate_LOT_NUMBER (
1176: P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1177: P_Validation_mode IN VARCHAR2,
1178: P_LOT_NUMBER IN VARCHAR2,
1179: -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY JTF_PLSQL_API.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
1180: X_Return_Status OUT NOCOPY VARCHAR2,
1181: X_Msg_Count OUT NOCOPY NUMBER,
1182: X_Msg_Data OUT NOCOPY VARCHAR2
1183: )
1193:
1194: -- Initialize API return status to SUCCESS
1195: x_return_status := FND_API.G_RET_STS_SUCCESS;
1196:
1197: IF(p_validation_mode = JTF_PLSQL_API.G_CREATE)
1198: THEN
1199: -- Hint: Validate data
1200: -- IF p_LOT_NUMBER is not NULL and p_LOT_NUMBER <> G_MISS_CHAR
1201: -- verify if data is valid
1200: -- IF p_LOT_NUMBER is not NULL and p_LOT_NUMBER <> G_MISS_CHAR
1201: -- verify if data is valid
1202: -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1203: NULL;
1204: ELSIF(p_validation_mode = JTF_PLSQL_API.G_UPDATE)
1205: THEN
1206: -- Hint: Validate data
1207: -- IF p_LOT_NUMBER <> G_MISS_CHAR
1208: -- verify if data is valid
1222: PROCEDURE Validate_SERIAL_NUMBER (
1223: P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1224: P_Validation_mode IN VARCHAR2,
1225: P_SERIAL_NUMBER IN VARCHAR2,
1226: -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY JTF_PLSQL_API.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
1227: X_Return_Status OUT NOCOPY VARCHAR2,
1228: X_Msg_Count OUT NOCOPY NUMBER,
1229: X_Msg_Data OUT NOCOPY VARCHAR2
1230: )
1240:
1241: -- Initialize API return status to SUCCESS
1242: x_return_status := FND_API.G_RET_STS_SUCCESS;
1243:
1244: IF(p_validation_mode = JTF_PLSQL_API.G_CREATE)
1245: THEN
1246: -- Hint: Validate data
1247: -- IF p_SERIAL_NUMBER is not NULL and p_SERIAL_NUMBER <> G_MISS_CHAR
1248: -- verify if data is valid
1247: -- IF p_SERIAL_NUMBER is not NULL and p_SERIAL_NUMBER <> G_MISS_CHAR
1248: -- verify if data is valid
1249: -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1250: NULL;
1251: ELSIF(p_validation_mode = JTF_PLSQL_API.G_UPDATE)
1252: THEN
1253: -- Hint: Validate data
1254: -- IF p_SERIAL_NUMBER <> G_MISS_CHAR
1255: -- verify if data is valid
1266: END Validate_SERIAL_NUMBER;
1267:
1268:
1269: -- Hint: inter-field level validation can be added here.
1270: -- Hint: If p_validation_mode = JTF_PLSQL_API.G_VALIDATE_UPDATE, we should use cursor
1271: -- to get old values for all fields used in inter-field validation and set all G_MISS_XXX fields to original value
1272: -- stored in database table.
1273: PROCEDURE Validate_plsl_rec(
1274: P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1296: -- THEN
1297: -- x_return_status := FND_API.G_RET_STS_ERROR;
1298:
1299: -- Debug Message
1300: JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'API_INVALID_RECORD');
1301:
1302: -- Standard call to get message count and if count is 1, get message info.
1303: FND_MSG_PUB.Count_And_Get
1304: ( p_count => x_msg_count,
1320: l_api_name CONSTANT VARCHAR2(30) := 'Validate_pack_serial_lots';
1321: BEGIN
1322:
1323: -- Debug Message
1324: JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: ' || l_api_name || 'start');
1325:
1326:
1327: -- Initialize API return status to SUCCESS
1328: x_return_status := FND_API.G_RET_STS_SUCCESS;
1326:
1327: -- Initialize API return status to SUCCESS
1328: x_return_status := FND_API.G_RET_STS_SUCCESS;
1329:
1330: IF (p_validation_level >= JTF_PLSQL_API.G_VALID_LEVEL_ITEM) THEN
1331: -- Hint: We provide validation procedure for every column. Developer should delete
1332: -- unnecessary validation procedures.
1333: Validate_PL_SERIAL_LOT_ID(
1334: p_init_msg_list => FND_API.G_FALSE,
1415: END IF;
1416:
1417: END IF;
1418:
1419: IF (p_validation_level >= JTF_PLSQL_API.G_VALID_LEVEL_RECORD) THEN
1420: -- Hint: Inter-field level validation can be added here
1421: -- invoke record level validation procedures
1422: Validate_plsl_Rec(
1423: p_init_msg_list => FND_API.G_FALSE,
1431: raise FND_API.G_EXC_ERROR;
1432: END IF;
1433: END IF;
1434:
1435: IF (p_validation_level >= JTF_PLSQL_API.G_VALID_LEVEL_INTER_RECORD) THEN
1436: -- invoke inter-record level validation procedures
1437: NULL;
1438: END IF;
1439:
1436: -- invoke inter-record level validation procedures
1437: NULL;
1438: END IF;
1439:
1440: IF (p_validation_level >= JTF_PLSQL_API.G_VALID_LEVEL_INTER_ENTITY) THEN
1441: -- invoke inter-entity level validation procedures
1442: NULL;
1443: END IF;
1444:
1443: END IF;
1444:
1445:
1446: -- Debug Message
1447: JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: ' || l_api_name || 'end');
1448:
1449: END Validate_pack_serial_lots;
1450:
1451: End CSP_Pack_Serial_Lots_PVT;