Home » SQL & PL/SQL » SQL & PL/SQL » creating a package
creating a package [message #580469] Mon, 25 March 2013 02:19 Go to next message
manaswini.m18@gmail.com
Messages: 1
Registered: March 2013
Location: Banglore
Junior Member
Hi friends following is the requirement
External Table
WKSHT_FILE_EXT
wksht_line
Export Table
Wksht_export
global_id varchar2(10)
wksht_line varchar2(250)

Export Map Table
wksht_export_map
EB varchar2(10)
global_id varchar2(10)

Sample Data to Insert
EB global ID
2B A01109
8P A12345
NE A34567


Step 1 Insert all records from the external table into the export table. Truncate the export table first

Step 2 Read in a record from the export map table

Step 3 Search through export table records looking for the key words BRANCH =. Compare the branch code with the branch code form the map table

Step 4 If a match is found mark all records in the export table for the worksheet with the global ID from the export map table as follows
The first line of a worksheet is marked by the words WKSHTS
The last line of the work sheet is marked by the words COMPANY CONFIDENTIAL
We will need to capture the line break so also mark the next line after the COMPANY CONFIDENTIAL line

Step 5 Continue with Steps 2 - 4 until all records have been processed from the export map table.

---------first I have to create a procedure ti insert data from external table to export table.
----Global id will be blank.it will be updated by the mapping table's Global Id when The EB COLUMN's data(i.e 8p,2Betc ) will match with the BRANC=NA,2Betc of the datasheet loaded from the external table..FOLLOWING IS THE SAMPLE DATASHEET

----------WKSHTS AAAAA BBBBBBBBBBB ELECTRONICS INC. TIME REPORT-DATE PAGE
SORT - BR, SLSREP AEC FIELD SALES REPRESENTATIVE 16:14 09/21/12 1
BRANCH = 2B


EMPLOYEE NAME SALVAAG, GREGG Days in the Month 28
LOC 2B OTE $153,499.95
SALES CODE D Salary $107,450.00
COMP PLAN B204 Monthly Target Bonus $3,542.30

Net GP Bonus ( 50%) $1,771.15
PEMCO GP$ Bonus ( 30%) $1,062.69

August 2012 Actuals and Plans
--------------------------------
Actual Plan Actual Plan
Direct NSB 1,167,425.00 2,033,902.00 PEMCO NSB 139,335.00 244,082.00
Direct Net GP$ 81,707.00 152,681.00 PEMCO NGP$ 9,702.00 17,906.00
Indirect NSB 1,843,259.00 1,940,266.00
Indirect Net GP$ 161,532.00 168,803.00
Total NSB 3,010,684.00 3,974,168.00
Total Net GP$ 243,239.00 321,484.00

Direct Net GP% 7.00 % 7.51 % PEMCO NGP% 6.96 % 7.34 %
Dir NGP% Variance .51-% PEMCO NGP% Variance .38-%



Net Gross Profit Bonus Calculation
----------------------------------
Net GP$ Achievement % 243,239.00 / 321,484.00 = 75.66%
Achievement Over Threshold 75.66% - 70.00% = 5.66%
Net GP$ Payout % 5.66%
Payout % 0.00% <= 70.00% 70.00% X 0.00 = 0.00%
> 70.00% to 75.66% 5.66% X 3.3334 = 18.8670%
------------
Total Payout % 18.8670%
Direct NGP% Achievement Factor 1.00%
Final GP$ Payout % 18.87% X 1.00 = 18.87%
Monthly GP$ Target Bonus $1,771.15
Monthly GP$ Bonus Earned $334.22
August GP Bonus Due $334.22

Net PEMCO Gross Profit Bonus Calculation
----------------------------------------
Net GP$ Achievement % 9,702.00 / 17,906.00 = 54.18%
Achievement Over Threshold 54.18% - 70.00% = 0.00%
Net GP$ Payout % 0.00%
Payout % 0.00% <= 70.00% 70.00% X 0.00 = 0.00%
Direct NGP% Achievement Factor 1.00%
Final GP$ Payout % 0.00% X 1.00 = 0.00%
Monthly GP$ Target Bonus $1,062.69
Monthly GP$ Bonus Earned $.00
August GP Bonus Due $.00

COMPANY CONFIDENTIAL


WKSHTS AAAAA BBBBBBBBBBB INC. TIME REPORT-DATE PAGE
SORT - BR, SLSREP AEC FIELD SALES REPRESENTATIVE 16:14 09/21/12 2
BRANCH = 8P


EMPLOYEE NAME AVILA, EDGAR Days in the Month 28
LOC 8P OTE $50,469.98
SALES CODE D Salary $35,329.00
COMP PLAN B204 Monthly Target Bonus $1,164.69

Net GP Bonus ( 50%) $582.35
PEMCO GP$ Bonus ( 30%) $349.41

August 2012 Actuals and Plans
--------------------------------
Actual Plan Actual Plan
Direct NSB 818,007.00 834,879.00 PEMCO NSB 142,417.00 127,347.00
Direct Net GP$ 146,759.00 155,426.00 PEMCO NGP$ 25,084.00 26,735.00
Indirect NSB 0.00 34,914.00
Indirect Net GP$ 0.00 6,285.00
Total NSB 818,007.00 869,793.00
Total Net GP$ 146,759.00 161,711.00

Direct Net GP% 17.94 % 18.62 % PEMCO NGP% 17.61 % 20.99 %
Dir NGP% Variance .68-% PEMCO NGP% Variance 3.38-%



Net Gross Profit Bonus Calculation
----------------------------------
Net GP$ Achievement % 146,759.00 / 161,711.00 = 90.75%
Achievement Over Threshold 90.75% - 70.00% = 20.75%
Net GP$ Payout % 20.75%
Payout % 0.00% <= 70.00% 70.00% X 0.00 = 0.00%
> 70.00% to 90.75% 20.75% X 3.3334 = 69.1681%
------------
Total Payout % 69.1681%
Direct NGP% Achievement Factor 1.00%
Final GP$ Payout % 69.17% X 1.00 = 69.17%
Monthly GP$ Target Bonus $582.35
Monthly GP$ Bonus Earned $402.81
August GP Bonus Due $402.81

Net PEMCO Gross Profit Bonus Calculation
----------------------------------------
Net GP$ Achievement % 25,084.00 / 26,735.00 = 93.82%
Achievement Over Threshold 93.82% - 70.00% = 23.82%
Net GP$ Payout % 23.82%
Payout % 0.00% <= 70.00% 70.00% X 0.00 = 0.00%
> 70.00% to 93.82% 23.82% X 3.3334 = 79.4016%
------------
Total Payout % 79.4016%
Direct NGP% Achievement Factor 1.00%
Final GP$ Payout % 79.40% X 1.00 = 79.40%
Monthly GP$ Target Bonus $349.41
Monthly GP$ Bonus Earned $277.43
August GP Bonus Due $277.43

COMPANY CONFIDENTIAL--------------

THERE ARE 2 pages..I have to split this LONG REPORT STORED IN WKSHT_LINE COLUMN OF EXPORT TABLE to 2 records..like wise 500 pages are there means 500 records.. AND THEN FIND BRANCH= after that which two words will come i.e NA,2B etc if it will MATCH WITH MAPPING TABLE"S EB COLUMN"S DATA,THEN MAPPING TABLE's GLOBAL ID WILL BE UPDATED TO EXPORT TABLE's GLOBAL ID WHICH IS BLANK
Re: creating a package [message #580471 is a reply to message #580469] Mon, 25 March 2013 02:38 Go to previous message
Michel Cadot
Messages: 59203
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel
Previous Topic: cummulative running balance by condition
Next Topic: rollback after commit
Goto Forum:
  


Current Time: Tue Sep 23 20:14:05 CDT 2014

Total time taken to generate the page: 0.07968 seconds