Disclaimer: Please bear with the long, detailed question. I want to make the requirement as clear as possible.
The below Oracle EBS seeded table (`PER_PEOPLE_EXTRA_INFO`) stores an employee's Extra Information:
| PERSON_EXTRA_INFO_ID | PERSON_ID | EXTRA_INFORMATION | INFORMATION_CATEGORY | INFORMATION1 | INFORMATION2 | INFORMATION3 | ...
| INFORMATION10 |
|---|
| 1 | 11111 | Academic Rank | Next Academic Rank | Professor | Y | 10.5 | ... | (NULL) |
| 2 | 11111 | Academic Rank | Academic Rank | Ass.Professor | N | (NULL) | ... | (NULL) |
| 3 | 11111 | Academic Rank | Effective Start Date | 01-JAN-2017 | (NULL) | (NULL) | ... | (NULL) |
| 4 | 11111 | Academic Rank | Effective End Date | 31-DEC-4712 | (NULL) | (NULL) | ... | (NULL) |
| 5 | 11111 | Alien Income Forecast | Income_code | P | (NULL) | (NULL) | ... | (NULL) |
| 6 | 11111 | Alien Income Forecast | Date | 2017 | 1 | DEC | ... | (NULL) |
| 7 | 11111 | Alien Income Forecast | Amount | 10000 | Tax | (NULL) | ... | (NULL) |
| 8 | 11111 | Alien Income Forecast | Effective Start Date | 01-JAN-2017 | (NULL) | (NULL) | ... | (NULL) |
| 9 | 11111 | Alien Income Forecast | Effective End Date | 31-DEC-4712 | (NULL) | (NULL) | ... | (NULL) |
| 10 | 22222 | Academic Rank | Next Academic Rank | Master | N | 11.5 | ... | (NULL) |
| 11 | 22222 | Academic Rank | Academic Rank | Professor | Y | (NULL) | ... | (NULL) |
| 12 | 22222 | Academic Rank | Effective Start Date | 01-JAN-2017 | (NULL) | (NULL) | ... | (NULL) |
| 13 | 22222 | Academic Rank | Effective End Date | 31-DEC-4712 | (NULL) | (NULL) | ... | (NULL) |
| 14 | 22222 | Alien Income Forecast | Income_code | X1 | (NULL) | (NULL) | ... | (NULL) |
| 15 | 22222 | Alien Income Forecast | Date | 2017 | 1 | APR | ... | (NULL) |
| 16 | 22222 | Alien Income Forecast | Amount | 100000000 | Tax-Free | (NULL) | ... | (NULL) |
| 17 | 22222 | Alien Income Forecast | Effective Start Date | 01-JAN-2017 | (NULL) | (NULL) | ... | (NULL) |
| 18 | 22222 | Alien Income Forecast | Effective End Date | 31-DEC-4712 | (NULL) | (NULL) | ... | (NULL) |
All Information Columns have the data type VARCHAR2, but some store dates (like in the above sample).
This table is being populated by a Oracle EBS Seeded API Package (HR_PERSON_EXTRA_INFO_API.CREATE_PERSON_EXTRA_INFO).
I am tasked to create an upload process that would use a CSV template and interface information to the API.
The below is a sample of the CSV Template:
| EMPLOYEE_NUMBER | Next Academic Rank | Max Academic Rank | Max Score | Academic Rank | Promotion Rank | Academic Rank Start Date | Academic Rank End Date | Income Code | Date (YYYY) | Date (MON) | Date (DD) | Amount | Tax? | Income Start Date | Income End Date |
|---|
| 33333 | Professor | Y | 10.1 | Ass.Professor | Y | 01-JAN-2017 | 31-DEC-4712 | P | 2017 | JAN | 1 | 10000 | Tax | 01-JAN-2017 | 31-DEC-4712 |
| 44444 | Ass.Professor | N | 9.7 | Student | N | 01-JAN-2017 | 31-DEC-4712 | R | 2017 | JAN | 1 | 50000 | Non Tax | 01-JAN-2017 | 31-DEC-4712 |
I'm planning to use an External Table to read the CSV Template and pass the information to the API And use something like the code block below (please note the comments):
DECLARE
CURSOR EXT_CUR IS
SELECT EMPLOYEE_NUMBER
, NEXT_ACADEMIC_RANK -- Academic Rank (Next Academic Rank) INFORMATION1
, MAX_ACADEMIC_RANK -- Academic Rank (Next Academic Rank) INFORMATION2
, MAX_SCORE -- Academic Rank (Next Academic Rank) INFORMATION3
, ACADEMIC_RANK -- Academic Rank (Academic Rank) INFORMATION1
, PROMOTION_RANK -- Academic Rank (Academic Rank) INFORMATION2
, ACADEMIC_RANK_START_DATE -- Academic Rank (Effective Start Date) INFORMATION1
, ACADEMIC_RANK_END_DATE -- Academic Rank (Effective End Date) INFORMATION1
, INCOME_CODE -- Alien Income Forecast (Income_code) INFORMATION1
, DATE_YYYY -- Alien Income Forecast (Date) INFORMATION1
, DATE_MON -- Alien Income Forecast (Date) INFORMATION2
, DATE_DD -- Alien Income Forecast (Date) INFORMATION3
, AMOUNT -- Alien Income Forecast (Amount) INFORMATION1
, TAX -- Alien Income Forecast (Amount) INFORMATION2
, INCOME_START_DATE -- Alien Income Forecast (Effective Start Date) INFORMATION1
, INCOME_END_DATE -- Alien Income Forecast (Effective End Date) INFORMATION1
FROM SAMPLE_EXTRA_INFO_EXT; -- this is the external table
BEGIN
FOR EXT_REC IN EXT_CUR LOOP
SAMPLE_PKG.CREATE_EXTRA_INFO
(
P_EMPLOYEE_NUMBER => EXT_REC.EMPLOYEE_NUMBER
, P_EXTRA_INFORMATION => EXT_REC.EXTRA_INFORMATION -- This is my problem, how do i make it vertical?
, P_INFORMATION_CATEGORY => EXT_REC.INFORMATION_CATEGORY
, P_INFORMATION1 => EXT_REC.INFORMATION1
, P_INFORMATION2 => EXT_REC.INFORMATION2
, P_INFORMATION3 => EXT_REC.INFORMATION3
, P_INFORMATION4 => EXT_REC.INFORMATION4
, P_INFORMATION5 => EXT_REC.INFORMATION5
, P_INFORMATION6 => EXT_REC.INFORMATION6
, P_INFORMATION7 => EXT_REC.INFORMATION7
, P_INFORMATION8 => EXT_REC.INFORMATION8
, P_INFORMATION9 => EXT_REC.INFORMATION9
, P_INFORMATION10 => EXT_REC.INFORMATION10
);
END LOOP;
END;
Here are my problems:
1. The template is "horizontal", but the API is "vertical".
2. Not all Extra Information use the same Information Columns (some use column INFORMATION1 - 3, some use only column INFORMATION1 ).
With that said, how can i transform the template above into a valid format that the API can use?
I was thinking of using Pivot or UnPivot in the External Table but i'm not quite sure on how to approach the requirement.
A Pure SQL approach is preferred, but using PL/SQL would be fine.
Oracle Version
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
"CORE 11.2.0.4.0 Production"
TNS for Solaris: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production