Dear Friends,
I am a newbie to PL/SQL and i got one small task to "take multiple input parameters" and print the corresponding "multiple output parameters". As the condition is to use only anonymous blocks (like only read access to Database), i am unable to build a procedure for following requirement. i tried to create the procedure by taking values and storing them into cursor but somehow i am unable to figure out the logic. Could you please help me with your inputs? Your help is really valuable for me:)
Here is the requirement description and data
Create an Anonymous procedure to take list of ORDERS as input (by using RECORD TYPE with IN OUT parameters if required),
Input: Give multiple orders and those order numbers should get appended with 'C'. If search not found, then with 'F'. This is mandatory because, in ORD table order will start with either C or F.
ex: 78849890 should get appended with C as C78849890. if not found in table, then it should be like F78849890
1) Check if the list of input orders (input: multiple order numbers) exist without any HAS_IMPORT_ERROR (i.e. = 0) in WM.ORD table. If yes, return summary (Boolean) on status.
2) If HAS_IMPORT_ERROR = '1' then print list of orders with routing issues (display ORDER_ID, TC_ORDER_ID,FIELD_NAME and DESCRIPTION) if in case of integration issues.
Table details and sample data
WM.ORD
TC_ORDER_ID VARCHAR2(50 CHAR)
ORDER_ID NUMBER(10)
DO_STATUS NOT NULL NUMBER(3)
CREATED_DTTM NOT NULL TIMESTAMP(6)
IS_IMPORTED NOT NULL NUMBER(1)
HAS_IMPORT_ERROR NOT NULL NUMBER(1)
DISTRO_NUMBER VARCHAR2(50 CHAR)
WM.ERR
ORDER_ID NUMBER(10)
FIELD_NAME VARCHAR2(40 CHAR)
DESCRIPTION VARCHAR2(500 CHAR)
SAMPLE DATA from WM.ORD table:
-------------------------------------------------
TC_ORDER_ID ORDER_ID DO_STATUS CREATED_SOURCE CREATED_DTTM IS_IMPORTED HAS_IMPORT_ERROR
C78849827 6301223 190 C 09.10.17 14:50:18.000000000 1 0
C78849890 6301225 190 C 09.10.17 14:50:20.000000000 1 0
C78849894 6301232 190 C 09.10.17 14:50:20.000000000 1 0
C78850505 6300967 190 C 09.10.17 14:49:14.000000000 1 1
C78850555 6300969 190 C 09.10.17 14:49:15.000000000 1 0
C78850925 6300973 190 C 09.10.17 14:49:15.000000000 1 1
C78850063 6300975 190 C 09.10.17 14:49:15.000000000 1 1
C78850741 6300978 190 C 09.10.17 14:49:15.000000000 1 1
C78850926 6300981 190 C 09.10.17 14:49:16.000000000 1 1
C78850935 6300983 190 C 09.10.17 14:49:16.000000000 1 0
SAMPLE DATA from WM.ERR table (ORDER_ID is the link between ORD and ERR table):
---------------------------------------------------------------------------------------------------------------------------
ORDER_ID FIELD_NAME DESCRIPTION
6300967 LineProfileName {0}: Invalid {1} {2}
6300973 destinationFacilityAliasIdString Distribution order destination address missing and no destination facility supplied
6300975 originFacilityAliasIdString Distribution order origin address missing and no origin facility supplied
6300978 destinationFacilityAliasIdString Distribution order destination address must have one of these fields populated: Street Address 1, Street Address 2, Street Address 3
6300981 destinationFacilityAliasIdString Distribution order destination address missing and no destination facility supplied
Do let me now with your inputs:). Thanks for helping out and i am sure that i can learn lot from this community.
Regards,
Veeresh